SEO

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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Joachim Wieland <joe@mcknight.de> 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

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:id)
(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

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:id)
> (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

Joachim Wieland <joe@mcknight.de> 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

Joachim Wieland <joe@mcknight.de> 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:id)
> (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:id)
(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:id)
(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

"Tom Lane" <tgl@sss.pgh.pa.us> 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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Gregory Stark <stark@enterprisedb.com> 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

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 es_ES@euro 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



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Joachim Wieland <joe@mcknight.de> 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:id)
> (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