Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit540e69a

Browse files
committed
Add an index on pg_inherits.inhparent, and use it to avoid seqscans in
find_inheritance_children(). This is a complete no-op in databases withoutany inheritance. In databases where there are just a few entries inpg_inherits, it could conceivably be a small loss. However, in databases withmany inheritance parents, it can be a big win.
1 parent649b5ec commit540e69a

File tree

4 files changed

+82
-27
lines changed

4 files changed

+82
-27
lines changed

‎src/backend/catalog/pg_inherits.c‎

Lines changed: 68 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -13,13 +13,15 @@
1313
*
1414
*
1515
* IDENTIFICATION
16-
* $PostgreSQL: pgsql/src/backend/catalog/pg_inherits.c,v 1.3 2009/06/11 14:48:55 momjian Exp $
16+
* $PostgreSQL: pgsql/src/backend/catalog/pg_inherits.c,v 1.4 2009/12/29 22:00:12 tgl Exp $
1717
*
1818
*-------------------------------------------------------------------------
1919
*/
2020
#include"postgres.h"
2121

22+
#include"access/genam.h"
2223
#include"access/heapam.h"
24+
#include"catalog/indexing.h"
2325
#include"catalog/pg_class.h"
2426
#include"catalog/pg_inherits.h"
2527
#include"catalog/pg_inherits_fn.h"
@@ -29,6 +31,8 @@
2931
#include"utils/syscache.h"
3032
#include"utils/tqual.h"
3133

34+
staticintoid_cmp(constvoid*p1,constvoid*p2);
35+
3236

3337
/*
3438
* find_inheritance_children
@@ -46,10 +50,14 @@ find_inheritance_children(Oid parentrelId, LOCKMODE lockmode)
4650
{
4751
List*list=NIL;
4852
Relationrelation;
49-
HeapScanDescscan;
53+
SysScanDescscan;
5054
ScanKeyDatakey[1];
5155
HeapTupleinheritsTuple;
5256
Oidinhrelid;
57+
Oid*oidarr;
58+
intmaxoids,
59+
numoids,
60+
i;
5361

5462
/*
5563
* Can skip the scan if pg_class shows the relation has never had a
@@ -59,21 +67,52 @@ find_inheritance_children(Oid parentrelId, LOCKMODE lockmode)
5967
returnNIL;
6068

6169
/*
62-
* XXX might be a good idea to create an index on pg_inherits' inhparent
63-
* field, so that we can use an indexscan instead of sequential scan here.
64-
* However, in typical databases pg_inherits won't have enough entries to
65-
* justify an indexscan...
70+
* Scan pg_inherits and build a working array of subclass OIDs.
6671
*/
72+
maxoids=32;
73+
oidarr= (Oid*)palloc(maxoids*sizeof(Oid));
74+
numoids=0;
75+
6776
relation=heap_open(InheritsRelationId,AccessShareLock);
77+
6878
ScanKeyInit(&key[0],
6979
Anum_pg_inherits_inhparent,
7080
BTEqualStrategyNumber,F_OIDEQ,
7181
ObjectIdGetDatum(parentrelId));
72-
scan=heap_beginscan(relation,SnapshotNow,1,key);
7382

74-
while ((inheritsTuple=heap_getnext(scan,ForwardScanDirection))!=NULL)
83+
scan=systable_beginscan(relation,InheritsParentIndexId, true,
84+
SnapshotNow,1,key);
85+
86+
while ((inheritsTuple=systable_getnext(scan))!=NULL)
7587
{
7688
inhrelid= ((Form_pg_inherits)GETSTRUCT(inheritsTuple))->inhrelid;
89+
if (numoids >=maxoids)
90+
{
91+
maxoids *=2;
92+
oidarr= (Oid*)repalloc(oidarr,maxoids*sizeof(Oid));
93+
}
94+
oidarr[numoids++]=inhrelid;
95+
}
96+
97+
systable_endscan(scan);
98+
99+
heap_close(relation,AccessShareLock);
100+
101+
/*
102+
* If we found more than one child, sort them by OID. This ensures
103+
* reasonably consistent behavior regardless of the vagaries of an
104+
* indexscan. This is important since we need to be sure all backends
105+
* lock children in the same order to avoid needless deadlocks.
106+
*/
107+
if (numoids>1)
108+
qsort(oidarr,numoids,sizeof(Oid),oid_cmp);
109+
110+
/*
111+
* Acquire locks and build the result list.
112+
*/
113+
for (i=0;i<numoids;i++)
114+
{
115+
inhrelid=oidarr[i];
77116

78117
if (lockmode!=NoLock)
79118
{
@@ -99,8 +138,7 @@ find_inheritance_children(Oid parentrelId, LOCKMODE lockmode)
99138
list=lappend_oid(list,inhrelid);
100139
}
101140

102-
heap_endscan(scan);
103-
heap_close(relation,AccessShareLock);
141+
pfree(oidarr);
104142

105143
returnlist;
106144
}
@@ -231,7 +269,7 @@ typeInheritsFrom(Oid subclassTypeId, Oid superclassTypeId)
231269
{
232270
Oidthis_relid=lfirst_oid(queue_item);
233271
ScanKeyDataskey;
234-
HeapScanDescinhscan;
272+
SysScanDescinhscan;
235273
HeapTupleinhtup;
236274

237275
/*
@@ -255,9 +293,10 @@ typeInheritsFrom(Oid subclassTypeId, Oid superclassTypeId)
255293
BTEqualStrategyNumber,F_OIDEQ,
256294
ObjectIdGetDatum(this_relid));
257295

258-
inhscan=heap_beginscan(inhrel,SnapshotNow,1,&skey);
296+
inhscan=systable_beginscan(inhrel,InheritsRelidSeqnoIndexId, true,
297+
SnapshotNow,1,&skey);
259298

260-
while ((inhtup=heap_getnext(inhscan,ForwardScanDirection))!=NULL)
299+
while ((inhtup=systable_getnext(inhscan))!=NULL)
261300
{
262301
Form_pg_inheritsinh= (Form_pg_inherits)GETSTRUCT(inhtup);
263302
Oidinhparent=inh->inhparent;
@@ -273,7 +312,7 @@ typeInheritsFrom(Oid subclassTypeId, Oid superclassTypeId)
273312
queue=lappend_oid(queue,inhparent);
274313
}
275314

276-
heap_endscan(inhscan);
315+
systable_endscan(inhscan);
277316

278317
if (result)
279318
break;
@@ -287,3 +326,18 @@ typeInheritsFrom(Oid subclassTypeId, Oid superclassTypeId)
287326

288327
returnresult;
289328
}
329+
330+
331+
/* qsort comparison function */
332+
staticint
333+
oid_cmp(constvoid*p1,constvoid*p2)
334+
{
335+
Oidv1=*((constOid*)p1);
336+
Oidv2=*((constOid*)p2);
337+
338+
if (v1<v2)
339+
return-1;
340+
if (v1>v2)
341+
return1;
342+
return0;
343+
}

‎src/backend/commands/tablecmds.c‎

Lines changed: 8 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.311 2009/12/23 16:43:43 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.312 2009/12/29 22:00:12 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1793,24 +1793,22 @@ StoreCatalogInheritance1(Oid relationId, Oid parentOid,
17931793
int16seqNumber,RelationinhRelation)
17941794
{
17951795
TupleDescdesc=RelationGetDescr(inhRelation);
1796-
Datumdatum[Natts_pg_inherits];
1797-
boolnullarr[Natts_pg_inherits];
1796+
Datumvalues[Natts_pg_inherits];
1797+
boolnulls[Natts_pg_inherits];
17981798
ObjectAddresschildobject,
17991799
parentobject;
18001800
HeapTupletuple;
18011801

18021802
/*
18031803
* Make the pg_inherits entry
18041804
*/
1805-
datum[0]=ObjectIdGetDatum(relationId);/* inhrelid */
1806-
datum[1]=ObjectIdGetDatum(parentOid);/* inhparent */
1807-
datum[2]=Int16GetDatum(seqNumber);/* inhseqno */
1805+
values[Anum_pg_inherits_inhrelid-1]=ObjectIdGetDatum(relationId);
1806+
values[Anum_pg_inherits_inhparent-1]=ObjectIdGetDatum(parentOid);
1807+
values[Anum_pg_inherits_inhseqno-1]=Int16GetDatum(seqNumber);
18081808

1809-
nullarr[0]= false;
1810-
nullarr[1]= false;
1811-
nullarr[2]= false;
1809+
memset(nulls,0,sizeof(nulls));
18121810

1813-
tuple=heap_form_tuple(desc,datum,nullarr);
1811+
tuple=heap_form_tuple(desc,values,nulls);
18141812

18151813
simple_heap_insert(inhRelation,tuple);
18161814

‎src/include/catalog/catversion.h‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.562 2009/12/2920:11:45 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.563 2009/12/2922:00:14 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200912281
56+
#defineCATALOG_VERSION_NO200912291
5757

5858
#endif

‎src/include/catalog/indexing.h‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/include/catalog/indexing.h,v 1.112 2009/12/2920:11:45 tgl Exp $
11+
* $PostgreSQL: pgsql/src/include/catalog/indexing.h,v 1.113 2009/12/2922:00:14 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -156,6 +156,9 @@ DECLARE_UNIQUE_INDEX(pg_index_indexrelid_index, 2679, on pg_index using btree(in
156156

157157
DECLARE_UNIQUE_INDEX(pg_inherits_relid_seqno_index,2680,onpg_inheritsusingbtree(inhrelidoid_ops,inhseqnoint4_ops));
158158
#defineInheritsRelidSeqnoIndexId 2680
159+
/* This following index is not used for a cache and is not unique */
160+
DECLARE_INDEX(pg_inherits_parent_index,2187,onpg_inheritsusingbtree(inhparentoid_ops));
161+
#defineInheritsParentIndexId 2187
159162

160163
DECLARE_UNIQUE_INDEX(pg_language_name_index,2681,onpg_languageusingbtree(lannamename_ops));
161164
#defineLanguageNameIndexId 2681

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp