Re: [PATCHES] cluster test
Joachim Wieland <[EMAIL PROTECTED]> writes: > 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.15 rows=1 width=64) >Filter: (conrelid = 54538::oid) > (2 rows) Hm, well, that's why it doesn't want to use a seqscan, but why is the estimate so high? I get 7.35 on my boxes, vs 8.27 (which does agree with yours) for the indexscans. Stranger and stranger. Would you try inserting a "vacuum verbose pg_constraint" into the test as well? Maybe that will tell something relevant. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] cluster test
On Fri, May 25, 2007 at 12:09:43PM -0400, Tom Lane wrote: > This is in the regression database after a completed regression run, so > it's possible that it's a bit different state from what's seen at the > instant the cluster test was running, but it sure looks like the > "expected" results are what you get from a seqscan. Would you force a > seqscan and see what EXPLAIN shows as the cost on your machine? I have appended the relevant parts of the modified cluster script. I haven't pointed out clear enough that I'm running this against a fresh database cluster, no vacuum, no other databases, no config changes, just initdb, postmaster startup and installcheck. I usually have [EMAIL PROTECTED] here but I get the same with locale C. Feel free to ask for more output. Oh, btw, its a regular 32bit box as well. Joachim QUERY PLAN --- Index Scan using pg_constraint_conrelid_index on pg_constraint (cost=0.00..8.27 rows=1 width=64) Index Cond: (conrelid = 54538::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 = 54538::oid) -> Bitmap Index Scan on pg_constraint_conrelid_index (cost=0.00..4.26 rows=1 width=0) Index Cond: (conrelid = 54538::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.15 rows=1 width=64) Filter: (conrelid = 54538::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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Seq scans status update
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > Here's a new version, all known issues are now fixed. I'm now happy with > this patch. > Next, I'll start looking at the latest version of Jeff's synchronized > scans patch. I'm a bit confused --- weren't you intending to review these in parallel because of the possible interactions? Do you think this should be applied now, or does it need to wait to see what happens with Jeff's patch? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Seq scans status update
Here's a new version, all known issues are now fixed. I'm now happy with
this patch.
Next, I'll start looking at the latest version of Jeff's synchronized
scans patch.
Bruce Momjian wrote:
Great. Based on this, do you have a patch that is ready to apply.
---
Heikki Linnakangas wrote:
Heikki Linnakangas wrote:
In any case, I'd like to see more test results before we make a
decision. I'm running tests with DBT-2 and a seq scan running in the
background to see if the cache-spoiling effect shows up. I'm also trying
to get hold of some bigger hardware to run on. Running these tests takes
some calendar time, but the hard work has already been done. I'm going
to start reviewing Jeff's synchronized scans patch now.
Here are the results of the DBT-2 tests:
http://community.enterprisedb.com/seqscan/imola/
In each of these tests, at the end of rampup a script is started that
issues a "SELECT COUNT(*) FROM stock" in a loop, with 2 minute delay
between end of previous query and start of next one.
The patch makes the seq scans go significantly faster. In the 1 hour
test period, the patched tests perform roughly 30-100% as many selects
as unpatched tests.
With 100 and 105 warehouses, it also significantly reduces the impact of
the seq scan on other queries; response times are lower with the patch.
With 120 warehouses the reduction of impact is not as clear, but when
you plot the response times it's still there (the plots on the "response
times charts"-page are useless because they're overwhelmed by the
checkpoint spike).
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Index: src/backend/access/heap/heapam.c
===
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/access/heap/heapam.c,v
retrieving revision 1.232
diff -c -r1.232 heapam.c
*** src/backend/access/heap/heapam.c 8 Apr 2007 01:26:27 - 1.232
--- src/backend/access/heap/heapam.c 25 May 2007 19:22:33 -
***
*** 83,88
--- 83,96
*/
scan->rs_nblocks = RelationGetNumberOfBlocks(scan->rs_rd);
+ /* A scan on a table smaller than shared_buffers is treated like random
+ * access, but bigger scans use the bulk read page replacement policy.
+ */
+ if (scan->rs_nblocks > NBuffers)
+ scan->rs_accesspattern = AP_BULKREAD;
+ else
+ scan->rs_accesspattern = AP_NORMAL;
+
scan->rs_inited = false;
scan->rs_ctup.t_data = NULL;
ItemPointerSetInvalid(&scan->rs_ctup.t_self);
***
*** 123,133
--- 131,146
Assert(page < scan->rs_nblocks);
+ /* Read the page with the right strategy */
+ SetAccessPattern(scan->rs_accesspattern);
+
scan->rs_cbuf = ReleaseAndReadBuffer(scan->rs_cbuf,
scan->rs_rd,
page);
scan->rs_cblock = page;
+ SetAccessPattern(AP_NORMAL);
+
if (!scan->rs_pageatatime)
return;
Index: src/backend/access/transam/xlog.c
===
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.268
diff -c -r1.268 xlog.c
*** src/backend/access/transam/xlog.c 30 Apr 2007 21:01:52 - 1.268
--- src/backend/access/transam/xlog.c 15 May 2007 16:23:30 -
***
*** 1668,1673
--- 1668,1700
}
/*
+ * Returns true if 'record' hasn't been flushed to disk yet.
+ */
+ bool
+ XLogNeedsFlush(XLogRecPtr record)
+ {
+ /* Quick exit if already known flushed */
+ if (XLByteLE(record, LogwrtResult.Flush))
+ return false;
+
+ /* read LogwrtResult and update local state */
+ {
+ /* use volatile pointer to prevent code rearrangement */
+ volatile XLogCtlData *xlogctl = XLogCtl;
+
+ SpinLockAcquire(&xlogctl->info_lck);
+ LogwrtResult = xlogctl->LogwrtResult;
+ SpinLockRelease(&xlogctl->info_lck);
+ }
+
+ /* check again */
+ if (XLByteLE(record, LogwrtResult.Flush))
+ return false;
+
+ return true;
+ }
+
+ /*
* Ensure that all XLOG data through the given position is flushed to disk.
*
* NOTE: this differs from XLogWrite mainly in that the WALWriteLock is not
Index: src/backend/commands/copy.c
===
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.283
diff -c -r1.283 copy.c
*** src/backend/commands/copy.c 27 Apr 2007 22:05:46 - 1.283
--- src/backend/commands/copy.c 15 May 2007 17:05:29 -
***
*** 1876,1881
--- 1876,1888
nfields = file_has_oids ? (attr_count + 1) : attr_count;
field_strings = (char **) palloc(nfields * sizeof(char *));
+ /* Use the special
Re: [PATCHES] Rewritten Index Advisor patch
I wrote:
> Attached is a proposed replacement patch that keeps essentially all the
> advisor logic outside the core backend, and uses the method I suggested of
> modifying the result of get_relation_info() rather than installing phony
> system-catalog entries.
I've applied this with one further change to make the planner_hook a bit
more general; it now looks like this:
PlannedStmt *
planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
{
PlannedStmt *result;
if (planner_hook)
result = (*planner_hook) (parse, cursorOptions, boundParams);
else
result = standard_planner(parse, cursorOptions, boundParams);
return result;
}
PlannedStmt *
standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
{
... exactly the same as planner() was before ...
}
This avoids presuming that the hook needs to duplicate the Query, and
potentially lets a plugin replace the planner altogether.
The Index Advisor patch as-submitted is not compatible with these hooks,
but it appears to need nontrivial work anyway due to other changes
between 8.2 and 8.3 (in particular the introduction of PlannedStmt).
Rather than trying to get it into 8.3 as a contrib module, I recommend
pursuing it as a pgfoundry project, so as to decouple its release
schedule from the backend's.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] cluster test
Gregory Stark <[EMAIL PROTECTED]> writes: > Perhaps this comes down to 64 vs 32 bit datum and aligments and therefore > different size tables which because the planner does the lseek to measure the > table size shows up as different estimates for sequential scan costs? But we've got plenty of both in the buildfarm, and none of them are showing this failure. So I'm curious to know what's really different about Joachim's installation. It seems he must have a pg_constraint table enough larger than "normal" to discourage the seqscan, but where did that come from? There's only one row in pg_constraint in standard template0 --- could he be working with a custom system that has many more? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] cluster test
"Tom Lane" <[EMAIL PROTECTED]> writes: > This is in the regression database after a completed regression run, so > it's possible that it's a bit different state from what's seen at the > instant the cluster test was running, but it sure looks like the > "expected" results are what you get from a seqscan. Would you force a > seqscan and see what EXPLAIN shows as the cost on your machine? Perhaps this comes down to 64 vs 32 bit datum and aligments and therefore different size tables which because the planner does the lseek to measure the table size shows up as different estimates for sequential scan costs? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] cluster test
Joachim Wieland <[EMAIL PROTECTED]> writes: > 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 = 170982::oid) > (2 rows) Actually, can the locale idea --- it looks like a plan-instability thing. On my machines I get results like this: regression=# explain SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass; QUERY PLAN -- Seq Scan on pg_constraint (cost=0.00..7.35 rows=1 width=64) Filter: (conrelid = 28856::oid) (2 rows) regression=# SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass; conname clstr_tst_pkey clstr_tst_con (2 rows) regression=# set enable_seqscan TO 0; SET regression=# 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 = 28856::oid) (2 rows) regression=# SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass; conname clstr_tst_con clstr_tst_pkey (2 rows) This is in the regression database after a completed regression run, so it's possible that it's a bit different state from what's seen at the instant the cluster test was running, but it sure looks like the "expected" results are what you get from a seqscan. Would you force a seqscan and see what EXPLAIN shows as the cost on your machine? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] cluster test
Joachim Wieland <[EMAIL PROTECTED]> writes: > As said before, it only happens with "make installcheck", not "make check". Curious. I'm not sure if the buildfarm tries to isolate the installation against its locale environment --- can you check the locale used by the install case? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] cluster test
Joachim Wieland wrote: > On Fri, May 25, 2007 at 10:33:41AM -0400, Tom Lane wrote: > > We should find out why that's happening rather than just throwing an > > ORDER BY at it. Considering the number of buildfarm machines that > > aren't showing any such problem, there must be something odd about > > yours. What's the platform? What plan is being chosen for that SELECT? > > It's regular Debian Linux 2.6 on ix86. > > 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 = 170982::oid) > (2 rows) > > > SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass; > conname > > clstr_tst_con > clstr_tst_pkey > (2 rows) > > As said before, it only happens with "make installcheck", not "make check". Maybe there's an autovacuum run just before the test that causes pg_constraint entries to be reordered? -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 Oh, oh, las chicas galacianas, lo harán por las perlas, ¡Y las de Arrakis por el agua! Pero si buscas damas Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] cluster test
On Fri, May 25, 2007 at 10:33:41AM -0400, Tom Lane wrote: > We should find out why that's happening rather than just throwing an > ORDER BY at it. Considering the number of buildfarm machines that > aren't showing any such problem, there must be something odd about > yours. What's the platform? What plan is being chosen for that SELECT? It's regular Debian Linux 2.6 on ix86. 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 = 170982::oid) (2 rows) SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass; conname clstr_tst_con clstr_tst_pkey (2 rows) As said before, it only happens with "make installcheck", not "make check". Joachim ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] cluster test
Joachim Wieland <[EMAIL PROTECTED]> writes: > For some reason the cluster test fails on my machine due to a different > order of the result rows when I run "installcheck" instead of "check". Is > there a problem adding an ORDER BY to it? We should find out why that's happening rather than just throwing an ORDER BY at it. Considering the number of buildfarm machines that aren't showing any such problem, there must be something odd about yours. What's the platform? What plan is being chosen for that SELECT? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] ecpg USE_INTEGER_DATETIMES missing in msvc build
The appended patch makes the msvc setting "integer_datetimes" propagate to
ecpg_config.h as well.
Joachim
Index: src/tools/msvc/Solution.pm
===
RCS file: /projects/cvsroot/pgsql/src/tools/msvc/Solution.pm,v
retrieving revision 1.25
diff -u -r1.25 Solution.pm
--- src/tools/msvc/Solution.pm 15 May 2007 01:57:57 - 1.25
+++ src/tools/msvc/Solution.pm 25 May 2007 12:38:04 -
@@ -247,8 +247,9 @@
#if (_MSC_VER > 1200)
#define HAVE_LONG_LONG_INT_64
#define ENABLE_THREAD_SAFETY 1
-#endif
EOF
+print O "#define USE_INTEGER_DATETIMES 1\n" if ($self->{options}->{integer_datetimes});
+print O "#endif\n";
close(O);
}
---(end of broadcast)---
TIP 6: explain analyze is your friend
[PATCHES] cluster test
For some reason the cluster test fails on my machine due to a different order of the result rows when I run "installcheck" instead of "check". Is there a problem adding an ORDER BY to it? Joachim Index: src/test/regress/sql/cluster.sql === RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/cluster.sql,v retrieving revision 1.11 diff -c -r1.11 cluster.sql *** src/test/regress/sql/cluster.sql 8 Apr 2007 01:26:33 - 1.11 --- src/test/regress/sql/cluster.sql 25 May 2007 12:20:34 - *** *** 73,79 -- Verify that foreign key link still works INSERT INTO clstr_tst (b, c) VALUES (, 'this should fail'); ! SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass; SELECT relname, relkind, --- 73,79 -- Verify that foreign key link still works INSERT INTO clstr_tst (b, c) VALUES (, 'this should fail'); ! SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass ORDER BY conname; SELECT relname, relkind, Index: src/test/regress/expected/cluster.out === RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/cluster.out,v retrieving revision 1.19 diff -c -r1.19 cluster.out *** src/test/regress/expected/cluster.out 8 Apr 2007 01:26:33 - 1.19 --- src/test/regress/expected/cluster.out 25 May 2007 12:20:34 - *** *** 249,259 INSERT INTO clstr_tst (b, c) VALUES (, 'this should fail'); ERROR: insert or update on table "clstr_tst" violates foreign key constraint "clstr_tst_con" DETAIL: Key (b)=() is not present in table "clstr_tst_s". ! SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass; conname - clstr_tst_pkey clstr_tst_con (2 rows) SELECT relname, relkind, --- 249,259 INSERT INTO clstr_tst (b, c) VALUES (, 'this should fail'); ERROR: insert or update on table "clstr_tst" violates foreign key constraint "clstr_tst_con" DETAIL: Key (b)=() is not present in table "clstr_tst_s". ! SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass ORDER BY conname; conname clstr_tst_con + clstr_tst_pkey (2 rows) SELECT relname, relkind, ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
