On Fri, May 25, 2007 at 05:58:58PM -0400, Tom Lane wrote:
> Would you try inserting a "vacuum verbose pg_constraint" into the test
> as well? Maybe that will tell something relevant.
It's weird. I have run a "make check" with the serial schedule and a regular
installcheck on a fresh DB. Vacuum verbose shows the same output on both but
different plans are chosen.
Joachim
VACUUM VERBOSE pg_constraint;
INFO: vacuuming "pg_catalog.pg_constraint"
INFO: scanned index "pg_constraint_conname_nsp_index" to remove 84 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "pg_constraint_conrelid_index" to remove 84 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "pg_constraint_contypid_index" to remove 84 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "pg_constraint_oid_index" to remove 84 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_constraint": removed 84 row versions in 3 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_constraint_conname_nsp_index" now contains 16 row versions in
2 pages
DETAIL: 84 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_constraint_conrelid_index" now contains 16 row versions in 2
pages
DETAIL: 84 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_constraint_contypid_index" now contains 16 row versions in 2
pages
DETAIL: 84 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_constraint_oid_index" now contains 16 row versions in 2 pages
DETAIL: 84 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_constraint": found 84 removable, 16 nonremovable row versions in 3
pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
3 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_2606"
INFO: index "pg_toast_2606_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_2606": found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
EXPLAIN SELECT conname FROM pg_constraint WHERE conrelid =
'clstr_tst'::regclass;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on pg_constraint (cost=0.00..3.20 rows=1 width=64)
Filter: (conrelid = 28867::oid)
(2 rows)
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass;
conname
----------------
clstr_tst_pkey
clstr_tst_con
(2 rows)
SET enable_indexscan = 0;
EXPLAIN SELECT conname FROM pg_constraint WHERE conrelid =
'clstr_tst'::regclass;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on pg_constraint (cost=0.00..3.20 rows=1 width=64)
Filter: (conrelid = 28867::oid)
(2 rows)
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass;
conname
----------------
clstr_tst_pkey
clstr_tst_con
(2 rows)
SET enable_bitmapscan = 0;
EXPLAIN SELECT conname FROM pg_constraint WHERE conrelid =
'clstr_tst'::regclass;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on pg_constraint (cost=0.00..3.20 rows=1 width=64)
Filter: (conrelid = 28867::oid)
(2 rows)
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass;
conname
----------------
clstr_tst_pkey
clstr_tst_con
(2 rows)
SET enable_indexscan = 1;
SET enable_bitmapscan = 1;
SELECT count(*) FROM pg_constraint;
count
-------
16
(1 row)
VACUUM VERBOSE pg_constraint;
INFO: vacuuming "pg_catalog.pg_constraint"
INFO: scanned index "pg_constraint_conname_nsp_index" to remove 84 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "pg_constraint_conrelid_index" to remove 84 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "pg_constraint_contypid_index" to remove 84 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "pg_constraint_oid_index" to remove 84 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_constraint": removed 84 row versions in 3 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_constraint_conname_nsp_index" now contains 16 row versions in
2 pages
DETAIL: 84 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_constraint_conrelid_index" now contains 16 row versions in 2
pages
DETAIL: 84 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_constraint_contypid_index" now contains 16 row versions in 2
pages
DETAIL: 84 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_constraint_oid_index" now contains 16 row versions in 2 pages
DETAIL: 84 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_constraint": found 84 removable, 16 nonremovable row versions in 3
pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
3 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_2606"
INFO: index "pg_toast_2606_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_2606": found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
EXPLAIN SELECT conname FROM pg_constraint WHERE conrelid =
'clstr_tst'::regclass;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Index Scan using pg_constraint_conrelid_index on pg_constraint
(cost=0.00..8.27 rows=1 width=64)
Index Cond: (conrelid = 28867::oid)
(2 rows)
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass;
conname
----------------
clstr_tst_con
clstr_tst_pkey
(2 rows)
SET enable_indexscan = 0;
EXPLAIN SELECT conname FROM pg_constraint WHERE conrelid =
'clstr_tst'::regclass;
QUERY PLAN
-------------------------------------------------------------------------------------------
Bitmap Heap Scan on pg_constraint (cost=4.26..8.27 rows=1 width=64)
Recheck Cond: (conrelid = 28867::oid)
-> Bitmap Index Scan on pg_constraint_conrelid_index (cost=0.00..4.26
rows=1 width=0)
Index Cond: (conrelid = 28867::oid)
(4 rows)
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass;
conname
----------------
clstr_tst_pkey
clstr_tst_con
(2 rows)
SET enable_bitmapscan = 0;
EXPLAIN SELECT conname FROM pg_constraint WHERE conrelid =
'clstr_tst'::regclass;
QUERY PLAN
---------------------------------------------------------------
Seq Scan on pg_constraint (cost=0.00..27.20 rows=1 width=64)
Filter: (conrelid = 28867::oid)
(2 rows)
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass;
conname
----------------
clstr_tst_pkey
clstr_tst_con
(2 rows)
SET enable_indexscan = 1;
SET enable_bitmapscan = 1;
SELECT count(*) FROM pg_constraint;
count
-------
16
(1 row)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
