473,387 Members | 1,463 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Weird query plan

Hi, everybody!

Here is a weird problem, I ran into...
I have two huge (80 million rows each) tables (a and b), with id as a PK
on both of them and also an FK from b referencing a.
When I try to run a query like:

select * from a, b where a.id >= 7901288 and a.id=b.id limit 1;

The query takes *forever*.
If I do
select * from a,b where b.id >= 7901288 and a.id=b.id limit 1;

then it returns right away.

The query plan looks identical in both cases:

Limit (cost=0.00..12.51 rows=1 width=8)
-> Nested Loop (cost=0.00..1009772807.91 rows=80740598 width=8)
-> Index Scan using b_pkey on b (cost=0.00..375410773.29
rows=80740598 width=4)
-> Index Scan using a_pkey on a (cost=0.00..6.85 rows=1 width=4)

.... which makes me think that it decides to use b as the outer table
for both cases (which would obviously make it suck in the first one)... :-(

This happens on 7.2.4... I have a 7.3 database with the same schema, but
it is not populated with data, so I could not test it on 7.3...
I looked at the 7.3's query plans though, and they look better to me:

Limit (cost=0.00..4.97 rows=1 width=8)
-> Nested Loop (cost=0.00..1657.34 rows=333 width=8)
-> Index Scan using b_pkey on b (cost=0.00..45.50 rows=333
width=4)
Index Cond: (id >= 7901288)
-> Index Scan using a_pkey on a (cost=0.00..4.82 rows=1 width=4)
Index Cond: (a.id = "outer".id)

in the second case, and

Limit (cost=0.00..4.97 rows=1 width=8)
-> Nested Loop (cost=0.00..1657.34 rows=333 width=8)
-> Index Scan using a_pkey on a (cost=0.00..45.50 rows=333
width=4)
Index Cond: (id >= 7901288)
-> Index Scan using b_pkey on b (cost=0.00..4.82 rows=1 width=4)
Index Cond: ("outer".id = b.id)

in the first case... (looks like it does swap them around as I expected)...

Do you know of anything that got fixed between 7.2.4 and 7.3, related to
this problem?

I also noticed that changing a,b to b,a in the from clause doesn't
affect anything... and (what's even more weird) even using an explicit
join doesn't help:
explain select a.duns from a natural join b dm where a.id >= 7901288
limit 1;
NOTICE: QUERY PLAN:

Limit (cost=0.00..12.78 rows=1 width=8)
-> Nested Loop (cost=0.00..1023061272.15 rows=80049919 width=8)
-> Index Scan using b_pkey on b (cost=0.00..380070641.01
rows=81786784 width=4)
-> Index Scan using a_pkey on a (cost=0.00..6.86 rows=1 width=4)

:-(

Any ideas?

Thanks a lot!

Dima

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #1
13 1937
Dmitry Tkach <dm****@openratings.com> writes:
The query plan looks identical in both cases: Limit (cost=0.00..12.51 rows=1 width=8)
-> Nested Loop (cost=0.00..1009772807.91 rows=80740598 width=8)
-> Index Scan using b_pkey on b (cost=0.00..375410773.29
rows=80740598 width=4)
-> Index Scan using a_pkey on a (cost=0.00..6.85 rows=1 width=4) ... which makes me think that it decides to use b as the outer table
for both cases (which would obviously make it suck in the first one)... :-(
That's what it says, all right, which seems odd to me. Are you sure you
looked at the right plans?
This happens on 7.2.4... I have a 7.3 database with the same schema, but
it is not populated with data, so I could not test it on 7.3...


I could not reproduce a problem on 7.2.4. I get (using toy tables, and
suppressing the planner's urge to use mergejoin instead)

lo=# explain select * from a, b where a.id >= 7901288 and a.id=b.id limit 1;
NOTICE: QUERY PLAN:

Limit (cost=0.00..4.97 rows=1 width=8)
-> Nested Loop (cost=0.00..1657.34 rows=333 width=8)
-> Index Scan using a_pkey on a (cost=0.00..45.50 rows=333 width=4)
-> Index Scan using b_pkey on b (cost=0.00..4.82 rows=1 width=4)

EXPLAIN
lo=# explain select * from a, b where b.id >= 7901288 and a.id=b.id limit 1;
NOTICE: QUERY PLAN:

Limit (cost=0.00..4.97 rows=1 width=8)
-> Nested Loop (cost=0.00..1657.34 rows=333 width=8)
-> Index Scan using b_pkey on b (cost=0.00..45.50 rows=333 width=4)
-> Index Scan using a_pkey on a (cost=0.00..4.82 rows=1 width=4)

EXPLAIN

which looks like the right thing.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #2
Hmmm...
Indeed. I tried it in 7.2.4 on a couple of empty tables, and it does do
the right thing...
Also, I have another copy (not exact copy, but identical schema, and
similar content... but about twice smaller) of the original database...
I tried my query on it, and it works right too.
So, there must be something wrong with that particular database I suppose...

Any ideas what I should look at?

Thanks a lot!

Dima

Tom Lane wrote:
Dmitry Tkach <dm****@openratings.com> writes:

The query plan looks identical in both cases:

Limit (cost=0.00..12.51 rows=1 width=8)
-> Nested Loop (cost=0.00..1009772807.91 rows=80740598 width=8)
-> Index Scan using b_pkey on b (cost=0.00..375410773.29
rows=80740598 width=4)
-> Index Scan using a_pkey on a (cost=0.00..6.85 rows=1 width=4)

... which makes me think that it decides to use b as the outer table
for both cases (which would obviously make it suck in the first one)... :-(


That's what it says, all right, which seems odd to me. Are you sure you
looked at the right plans?
This happens on 7.2.4... I have a 7.3 database with the same schema, but
it is not populated with data, so I could not test it on 7.3...


I could not reproduce a problem on 7.2.4. I get (using toy tables, and
suppressing the planner's urge to use mergejoin instead)

lo=# explain select * from a, b where a.id >= 7901288 and a.id=b.id limit 1;
NOTICE: QUERY PLAN:

Limit (cost=0.00..4.97 rows=1 width=8)
-> Nested Loop (cost=0.00..1657.34 rows=333 width=8)
-> Index Scan using a_pkey on a (cost=0.00..45.50 rows=333 width=4)
-> Index Scan using b_pkey on b (cost=0.00..4.82 rows=1 width=4)

EXPLAIN
lo=# explain select * from a, b where b.id >= 7901288 and a.id=b.id limit 1;
NOTICE: QUERY PLAN:

Limit (cost=0.00..4.97 rows=1 width=8)
-> Nested Loop (cost=0.00..1657.34 rows=333 width=8)
-> Index Scan using b_pkey on b (cost=0.00..45.50 rows=333 width=4)
-> Index Scan using a_pkey on a (cost=0.00..4.82 rows=1 width=4)

EXPLAIN

which looks like the right thing.

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #3
Dmitry Tkach <dm****@openratings.com> writes:
Also, I have another copy (not exact copy, but identical schema, and
similar content... but about twice smaller) of the original database...
I tried my query on it, and it works right too. So, there must be something wrong with that particular database I suppose...


Hmm. Take a look at the pg_stats statistics for the id columns in each
case. Could the ones for the misbehaving tables be out of whack
somehow? I'm wondering for example if the planner discounted the >=
condition because it thought it would match all the rows.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #4
Tom Lane wrote:
Dmitry Tkach <dm****@openratings.com> writes:

Also, I have another copy (not exact copy, but identical schema, and
similar content... but about twice smaller) of the original database...
I tried my query on it, and it works right too.

So, there must be something wrong with that particular database I suppose...


Hmm. Take a look at the pg_stats statistics for the id columns in each
case. Could the ones for the misbehaving tables be out of whack
somehow? I'm wondering for example if the planner discounted the >=
condition because it thought it would match all the rows.

Well... It *does* match (almost) all the rows (there are about a million
rows before that key, and the remaining 79 mil after)...
The stats look in synch with that:

for a:
stavalues1 |
{1000488,33495482,69111011,99286820,129611281,2044 41828,331968789,508451171,782660252,869480434,9897 87700}
for b:
stavalues1 |
{1008692,54892364,110119463,192551141,300490851,38 9609207,465139533,570442801,706876547,849087358,98 9851076}

(The key in the criteria was 7901288 - somewhere in the first bucket)
*But* isn't my 'limit' clause supposed to affect that decision? I mean,
even though the filter isn't very selective, it should still speed up
getting the *first* match...
Thanks!

Dima

P.S. I also tried to look at the stats of that other database I
mentioned... The stats for b look similar:
stavalues1 |
{1028104,25100079,50685614,78032989,105221902,1358 32793,199827486,611968165,807597786,884897604,9699 71779}

But the stats for a are just *not there at all* (is it even possible?)
Could it be the reason why it works on that database (because it uses
the default stats instead of the real thing)?


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #5
> P.S. I also tried to look at the stats of that other database I
mentioned... The stats for b look similar:
stavalues1 |
{1028104,25100079,50685614,78032989,105221902,1358 32793,199827486,611968165,807597786,884897604,9699 71779}
But the stats for a are just *not there at all* (is it even possible?)
Could it be the reason why it works on that database (because it uses
the default stats instead of the real thing)?

I ran 'analyze a;' on that database...
I now have the same problem with it, but the other way around - the
query with a condition on a runs quickly, and one with a condition on b
does not... and the query plans are the same, and have a as outer table...
The new stats for a look like:
stavalues1 |
{1003284,61663485,126262679,211106732,300624079,39 2709544,469196539,572479496,697890767,842087009,98 9170923}
.... but actually, I never tried it with a condition on b on that
database before analyze :-(
I just tried the "a - variant" (the one that wasn't working originally),
and it worked...
Now, I tried deleting all the entries for a from pg_statistic, and
running the b-variant... and it still doesn't work.

So, it probably has nothing to do with that analyze I ran...

Dima
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #6
Dmitry Tkach <dm****@openratings.com> writes:
I now have the same problem with it, but the other way around - the
query with a condition on a runs quickly, and one with a condition on b
does not... and the query plans are the same, and have a as outer table...


Hm. You know, I wonder whether the query plans really are the same.
One of the reasons why 7.3 and later show the qual conditions is that
it was too easy to fall into the trap of assuming that plans of the same
structure had the same conditions. Try doing "explain verbose" and look
to see if the plans look the same at that level of detail.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #7
Tom Lane wrote:
Dmitry Tkach <dm****@openratings.com> writes:

I now have the same problem with it, but the other way around - the
query with a condition on a runs quickly, and one with a condition on b
does not... and the query plans are the same, and have a as outer table...


Hm. You know, I wonder whether the query plans really are the same.
One of the reasons why 7.3 and later show the qual conditions is that
it was too easy to fall into the trap of assuming that plans of the same
structure had the same conditions. Try doing "explain verbose" and look
to see if the plans look the same at that level of detail.

regards, tom lane

I am afraid that's too much detail for me to look at :-)
I have no clue what all that stuff means :-(

But I am pretty sure they are the same - I did not know whether the
first one or the second was supposed to be the outer relation, so I
looked at it in the debugger...
So, I know that in both cases it was using b for the outer loop...

Thanks!
Dima
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #8
Here is that verbose output, if it is of any help:

prod=# explain verbose select a.id from a, b where a.id >= 7901288 and
b.id=a.id limit 1;
NOTICE: QUERY DUMP:

{ LIMIT :startup_cost 0.00 :total_cost 12.78 :rows 1 :width 8
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname duns :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree {
NESTLOOP :startup_cost 0.00 :total_cost 1023061272.15 :rows 80049919
:width 8 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
23 :restypmod -1 :resname duns :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 65000 :varattno 1 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <>
:lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 380070641.01 :rows
81786784 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1
:restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 2 :varattno 1
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1}})
:qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
:nprm 0 :scanrelid 2 :indxid ( 708140136) :indxqual (<>) :indxqualorig
(<>) :indxorderdir 1 } :righttree { INDEXSCAN :startup_cost 0.00
:total_cost 6.86 :rows 1 :width 4 :qptargetlist ({ TARGETENTRY :resdom {
RESDOM :resno 1 :restype 23 :restypmod -1 :resname <> :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 1}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm
() :initplan <> :nprm 0 :scanrelid 1 :indxid ( 1074605180) :indxqual
(({ EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { VAR :varno
65001 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2
:varoattno 1})} { EXPR :typeOid 16 :opType op :oper { OPER :opno 525
:opid 150 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype
23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST
:consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [ 104 -112 120 0 ] })})) :indxqualorig (({ EXPR :typeOid
16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args
({ VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup
0 :varnoold 2 :varoattno 1} { VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1})} { EXPR
:typeOid 16 :opType op :oper { OPER :opno 525 :opid 150 :opresulttype
16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 23 :constlen
4 :constbyval true :constisnull false :constvalue 4 [ 104 -112 120 0 ]
})})) :indxorderdir 0 } :extprm () :locprm () :initplan <> :nprm 0
:jointype 0 :joinqual <>} :righttree <> :extprm () :locprm () :initplan
<> :nprm 0 :limitOffset <> :limitCount { CONST :consttype 23 :constlen
4 :constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] }}
NOTICE: QUERY PLAN:

Limit (cost=0.00..12.78 rows=1 width=8)
-> Nested Loop (cost=0.00..1023061272.15 rows=80049919 width=8)
-> Index Scan using b_pkey on b (cost=0.00..380070641.01
rows=81786784 width=4)
-> Index Scan using a_pkey on a (cost=0.00..6.86 rows=1 width=4)

EXPLAIN
prod=# explain verbose select a.id from a,b where b.id >= 7901288 and
b.id=a.id limit 1;
NOTICE: QUERY DUMP:

{ LIMIT :startup_cost 0.00 :total_cost 12.51 :rows 1 :width 8
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname duns :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree {
NESTLOOP :startup_cost 0.00 :total_cost 1009772807.91 :rows 80740598
:width 8 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
23 :restypmod -1 :resname duns :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 65000 :varattno 1 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <>
:lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 375410773.29 :rows
80740598 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1
:restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 2 :varattno 1
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1}})
:qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
:nprm 0 :scanrelid 2 :indxid ( 708140136) :indxqual (({ EXPR :typeOid
16 :opType op :oper { OPER :opno 525 :opid 150 :opresulttype 16 } :args
({ VAR :varno 2 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 2 :varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull false :constvalue 4 [ 104 -112 120 0 ] })}))
:indxqualorig (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 525
:opid 150 :opresulttype 16 } :args ({ VAR :varno 2 :varattno 1 :vartype
23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1} { CONST
:consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [ 104 -112 120 0 ] })})) :indxorderdir 1 } :righttree {
INDEXSCAN :startup_cost 0.00 :total_cost 6.85 :rows 1 :width 4
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1
:indxid ( 1074605180) :indxqual (({ EXPR :typeOid 16 :opType op :oper {
OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 1} { VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 2 :varoattno 1})})) :indxqualorig (({ EXPR
:typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16
} :args ({ VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 2 :varoattno 1} { VAR :varno 1 :varattno 1
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1})}))
:indxorderdir 0 } :extprm () :locprm () :initplan <> :nprm 0 :jointype
0 :joinqual <>} :righttree <> :extprm () :locprm () :initplan <> :nprm
0 :limitOffset <> :limitCount { CONST :consttype 23 :constlen 4
:constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] }}
NOTICE: QUERY PLAN:

Limit (cost=0.00..12.51 rows=1 width=8)
-> Nested Loop (cost=0.00..1009772807.91 rows=80740598 width=8)
-> Index Scan using b_pkey on b (cost=0.00..375410773.29
rows=80740598 width=4)
-> Index Scan using a_pkey on a (cost=0.00..6.85 rows=1 width=4)

EXPLAIN
Dmitry Tkach wrote:
Tom Lane wrote:
Dmitry Tkach <dm****@openratings.com> writes:

I now have the same problem with it, but the other way around - the
query with a condition on a runs quickly, and one with a condition
on b does not... and the query plans are the same, and have a as
outer table...

Hm. You know, I wonder whether the query plans really are the same.
One of the reasons why 7.3 and later show the qual conditions is that
it was too easy to fall into the trap of assuming that plans of the same
structure had the same conditions. Try doing "explain verbose" and look
to see if the plans look the same at that level of detail.

regards, tom lane

I am afraid that's too much detail for me to look at :-)
I have no clue what all that stuff means :-(

But I am pretty sure they are the same - I did not know whether the
first one or the second was supposed to be the outer relation, so I
looked at it in the debugger...
So, I know that in both cases it was using b for the outer loop...

Thanks!
Dima


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #9
On Wed, Sep 17, 2003 at 04:39:59PM -0400, Dmitry Tkach wrote:
Here is that verbose output, if it is of any help:

prod=# explain verbose select a.id from a, b where a.id >= 7901288 and
b.id=a.id limit 1;
NOTICE: QUERY DUMP:


[etc]

Maybe you can try the indented version and do the comparison using diff?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Hoy es el primer dia del resto de mi vida"

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

Nov 11 '05 #10
Dmitry Tkach <dm****@openratings.com> writes:
Hm. You know, I wonder whether the query plans really are the same.
One of the reasons why 7.3 and later show the qual conditions is that
it was too easy to fall into the trap of assuming that plans of the same
structure had the same conditions. Try doing "explain verbose" and look
to see if the plans look the same at that level of detail.
I am afraid that's too much detail for me to look at :-)
I have no clue what all that stuff means :-(


Well, "diff" would've been enough to tell you they are different. It
looks to me like in the slow case the query plan is of the form

Limit
-> Nested Loop
-> Index Scan using a_pkey on a
<<no index condition, ie full table scan>>
-> Index Scan using b_pkey on b
b.id >= 7901288 and b.id = "outer".id

which is a pretty silly plan to arrive at; are you by any chance running
with enable_seqscan = off?

I'm not certain why 7.2 would be picking this plan, esp. given that it
is capable of generating the better plan. It might be that it's got
something to do with the low selectivity of the id >= 7901288 clause.
In any case, if it's fixed in 7.3 I'm not going to worry too much about
it ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #11
There isn't a formatter for these plans is there? Or some template for a
unix editor that will format it?

Dmitry Tkach wrote:
Here is that verbose output, if it is of any help:

prod=# explain verbose select a.id from a, b where a.id >= 7901288 and
b.id=a.id limit 1;
NOTICE: QUERY DUMP:

{ LIMIT :startup_cost 0.00 :total_cost 12.78 :rows 1 :width 8
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname duns :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <>
:lefttree { NESTLOOP :startup_cost 0.00 :total_cost 1023061272.15
:rows 80049919 :width 8 :qptargetlist ({ TARGETENTRY :resdom { RESDOM
:resno 1 :restype 23 :restypmod -1 :resname duns :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000 :varattno
1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno
1}}) :qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00 :total_cost
380070641.01 :rows 81786784 :width 4 :qptargetlist ({ TARGETENTRY
:resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname <>
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 2 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 2 :varoattno 1}}) :qpqual <> :lefttree <> :righttree <>
:extprm () :locprm () :initplan <> :nprm 0 :scanrelid 2 :indxid (
708140136) :indxqual (<>) :indxqualorig (<>) :indxorderdir 1 }
:righttree { INDEXSCAN :startup_cost 0.00 :total_cost 6.86 :rows 1
:width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1
:restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}})
:qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan
<> :nprm 0 :scanrelid 1 :indxid ( 1074605180) :indxqual (({ EXPR
:typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype
16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 1} { VAR :varno 65001 :varattno
1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno
1})} { EXPR :typeOid 16 :opType op :oper { OPER :opno 525 :opid 150
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST
:consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [ 104 -112 120 0 ] })})) :indxqualorig (({ EXPR
:typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype
16 } :args ({ VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 2 :varoattno 1} { VAR :varno 1 :varattno 1
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1})}
{ EXPR :typeOid 16 :opType op :oper { OPER :opno 525 :opid 150
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST
:consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [ 104 -112 120 0 ] })})) :indxorderdir 0 } :extprm ()
:locprm () :initplan <> :nprm 0 :jointype 0 :joinqual <>} :righttree
<> :extprm () :locprm () :initplan <> :nprm 0 :limitOffset <>
:limitCount { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue 4 [ 1 0 0 0 ] }}
NOTICE: QUERY PLAN:

Limit (cost=0.00..12.78 rows=1 width=8)
-> Nested Loop (cost=0.00..1023061272.15 rows=80049919 width=8)
-> Index Scan using b_pkey on b (cost=0.00..380070641.01
rows=81786784 width=4)
-> Index Scan using a_pkey on a (cost=0.00..6.86 rows=1 width=4)

EXPLAIN
prod=# explain verbose select a.id from a,b where b.id >= 7901288 and
b.id=a.id limit 1;
NOTICE: QUERY DUMP:

{ LIMIT :startup_cost 0.00 :total_cost 12.51 :rows 1 :width 8
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname duns :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <>
:lefttree { NESTLOOP :startup_cost 0.00 :total_cost 1009772807.91
:rows 80740598 :width 8 :qptargetlist ({ TARGETENTRY :resdom { RESDOM
:resno 1 :restype 23 :restypmod -1 :resname duns :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000 :varattno
1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno
1}}) :qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00 :total_cost
375410773.29 :rows 80740598 :width 4 :qptargetlist ({ TARGETENTRY
:resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname <>
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 2 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 2 :varoattno 1}}) :qpqual <> :lefttree <> :righttree <>
:extprm () :locprm () :initplan <> :nprm 0 :scanrelid 2 :indxid (
708140136) :indxqual (({ EXPR :typeOid 16 :opType op :oper { OPER
:opno 525 :opid 150 :opresulttype 16 } :args ({ VAR :varno 2 :varattno
1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1}
{ CONST :consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [ 104 -112 120 0 ] })})) :indxqualorig (({ EXPR
:typeOid 16 :opType op :oper { OPER :opno 525 :opid 150 :opresulttype
16 } :args ({ VAR :varno 2 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 2 :varoattno 1} { CONST :consttype 23
:constlen 4 :constbyval true :constisnull false :constvalue 4 [ 104
-112 120 0 ] })})) :indxorderdir 1 } :righttree { INDEXSCAN
:startup_cost 0.00 :total_cost 6.85 :rows 1 :width 4 :qptargetlist ({
TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1
:resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1
:indxid ( 1074605180) :indxqual (({ EXPR :typeOid 16 :opType op :oper
{ OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 1} { VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod
-1 :varlevelsup 0 :varnoold 2 :varoattno 1})})) :indxqualorig (({
EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65
:opresulttype 16 } :args ({ VAR :varno 65001 :varattno 1 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1} { VAR :varno 1
:varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 1})})) :indxorderdir 0 } :extprm () :locprm () :initplan <>
:nprm 0 :jointype 0 :joinqual <>} :righttree <> :extprm () :locprm ()
:initplan <> :nprm 0 :limitOffset <> :limitCount { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 1
0 0 0 ] }}
NOTICE: QUERY PLAN:

Limit (cost=0.00..12.51 rows=1 width=8)
-> Nested Loop (cost=0.00..1009772807.91 rows=80740598 width=8)
-> Index Scan using b_pkey on b (cost=0.00..375410773.29
rows=80740598 width=4)
-> Index Scan using a_pkey on a (cost=0.00..6.85 rows=1 width=4)

EXPLAIN
Dmitry Tkach wrote:
Tom Lane wrote:
Dmitry Tkach <dm****@openratings.com> writes:
I now have the same problem with it, but the other way around - the
query with a condition on a runs quickly, and one with a condition
on b does not... and the query plans are the same, and have a as
outer table...


Hm. You know, I wonder whether the query plans really are the same.
One of the reasons why 7.3 and later show the qual conditions is that
it was too easy to fall into the trap of assuming that plans of the
same
structure had the same conditions. Try doing "explain verbose" and
look
to see if the plans look the same at that level of detail.

regards, tom lane

I am afraid that's too much detail for me to look at :-)
I have no clue what all that stuff means :-(

But I am pretty sure they are the same - I did not know whether the
first one or the second was supposed to be the outer relation, so I
looked at it in the debugger...
So, I know that in both cases it was using b for the outer loop...

Thanks!
Dima


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #12
Dennis Gearon <ge*****@fireserve.net> writes:
There isn't a formatter for these plans is there?


Sure. In 7.2 I think you have to set debug_pretty_print and then look
in the postmaster log (debug_pretty_print didn't affect client output
for some reason). Later versions have progressively saner ways to get
the formatted results.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #13
Tom Lane wrote:
Dmitry Tkach <dm****@openratings.com> writes:

Hm. You know, I wonder whether the query plans really are the same.
One of the reasons why 7.3 and later show the qual conditions is that
it was too easy to fall into the trap of assuming that plans of the same
structure had the same conditions. Try doing "explain verbose" and look
to see if the plans look the same at that level of detail.


I am afraid that's too much detail for me to look at :-)
I have no clue what all that stuff means :-(


Well, "diff" would've been enough to tell you they are different. It
looks to me like in the slow case the query plan is of the form

Limit
-> Nested Loop
-> Index Scan using a_pkey on a
<<no index condition, ie full table scan>>
-> Index Scan using b_pkey on b
b.id >= 7901288 and b.id = "outer".id

which is a pretty silly plan to arrive at; are you by any chance running
with enable_seqscan = off?

Ummm... yes, I am.
Sorry, I should have mentioned that earlier...

I'm not certain why 7.2 would be picking this plan, esp. given that it
is capable of generating the better plan. It might be that it's got
something to do with the low selectivity of the id >= 7901288 clause.
In any case, if it's fixed in 7.3 I'm not going to worry too much about
it ...

I don't know that actually - the 7.3 database I tried it on when it
worked was empty .... I just assumed, that since it generated the right
query plan it would work...
But then, I fugured out that it worked in 7.2 as well when I tried it
with just two empty tables :-(
Dima

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: jwbeaty | last post by:
Here's a weird one. I'm running SQL Server 7 and when I run a backup something weird happens. When I perform the backup via Enterprise Manager by right clicking on the database I want to...
3
by: Paul Mateer | last post by:
Hi, I have been running some queries against a table in a my database and have noted an odd (at least it seems odd to me) performance issue. The table has approximately 5 million rows and...
3
by: Philip Yale | last post by:
I'm very puzzled by the choice of NC index being made by the optimizer in this example. I don't actually think it should use an NC index at all. I have: Table: CustomerStatus_T Single data...
0
by: apb18 | last post by:
A bit of query plan strangeness. Suppose you have an inheritance tree such that the columns 'ID' and 'field' appear in the top level table, call that table XXX. tables YYY and ZZZ both inherit...
6
by: Ryan | last post by:
I came across a situation that I've been unable to explain and was hoping somebody had an answer: I had written an update query which was taking about 8 seconds to run and considered it too...
2
by: Shawn | last post by:
I have a dataset named "dsMessages" that has a single table. I have a timer that runs a query against an SQL db every 5 seconds. At the beginning I have it run dsMessages.Clear(). The first time...
1
by: lists | last post by:
When using the (tbl).field notation for selecting a specific field from a composite field then the query returning the field is executed once per field. An example is giving below. The runtime is...
7
by: stig | last post by:
hi. coming from postgresql, i am used to textual references to most of the things i do with the database. i feel a little lost with all the graphical. i have few questions regarding MS SQL 2000...
6
by: DBMonitor | last post by:
I have a table on a database that contains 18million records. I need to design a system that queries this table to produce fast counts. I have got counts for multiple criteria down to only a few...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.