Re: [PERFORM] Optimizer seems to be way off, why?
Dirk Lutzebäck wrote:
> Richard Huxton wrote:
>
>> Dirk Lutzebäck wrote:
>>
>>> Hi,
>>>
>>> I do not under stand the following explain output (pgsql 8.0.3):
>>>
>>> explain analyze
>>> select b.e from b, d
>>> where b.r=516081780 and b.c=513652057 and b.e=d.e;
>>>
>>> QUERY PLAN
>>>
>>>
>>> Nested Loop (cost=0.00..1220.09 rows=1 width=4) (actual
>>> time=0.213..2926.845 rows=324503 loops=1)
>>> -> Index Scan using b_index on b (cost=0.00..1199.12 rows=1
>>> width=4) (actual time=0.104..17.418 rows=3293 loops=1)
>>> Index Cond: (r = 516081780::oid)
>>> Filter: (c = 513652057::oid)
>>> -> Index Scan using d_e_index on d (cost=0.00..19.22 rows=140
>>> width=4) (actual time=0.009..0.380 rows=99 loops=3293)
>>> Index Cond: ("outer".e = d.e)
>>> Total runtime: 3638.783 ms
>>> (7 rows)
>>>
>>> Why is the rows estimate for b_index and the nested loop 1? It is
>>> actually 3293 and 324503.
>>
>>
>>
>> I'm guessing (and that's all it is) that b.r and b.c have a higher
>> correlation than the planner is expecting. That is, it expects the
>> b.c=... to reduce the number of matching rows much more than it is.
>>
>> Try a query just on WHERE b.r=516081780 and see if it gets the
>> estimate right for that.
>>
>> If it's a common query, it might be worth an index on (r,c)
>>
>> --
>> Richard Huxton
>> Archonet Ltd
>>
>
> Thanks Richard, dropping the join for b.c now gives better estimates (it
> also uses a different index now) although not accurate (off by factor
> 10). This query is embedded in a larger query which now got a 1000 times
> speed up (!) because I can drop b.c because it is redundant.
Well, part of the problem is that the poorly estimated row is not 'b.e'
but 'b.r', it expects to only find one row that matches, and instead
finds 3293 rows.
Now, that *could* be because it mis-estimates the selectivity of b.r & b.c.
It actually estimated the join with d approximately correctly. (It
thought that for each row it would find 140, and it averaged 99).
>
> Though, why can't the planner see this correlation? I think somebody
> said the planner does not know about multiple column correlations, does it?
The planner does not maintain cross-column statistics, so you are
correct. I believe it assumes distributions are independent. So that if
r=R is 10% selective, and c= is 20% selective, the total
selectivity of r= AND c= is 2%. I could be wrong on this, but I
think it is approximately correct.
Now if you created the index on b(r,c), then it would have a much better
idea of how selective that would be. At the very least, it could index
on (r,c) rather than indexing on (r) and filtering by (c).
Also, if you have very skewed data (where you have 1 value 100k times,
and 50 values only 10times each), the planner can overestimate the low
values, and underestimate the high one. (It uses random sampling, so it
kind of depends where the entries are.)
Have you tried increasing the statistics on b.r and or b.c? Do you have
an index on b.c or just b.r?
To see what the planner thinks, you might try:
EXPLAIN ANALYZE
select count(*) from b where r=516081780;
That would tell you how selective the planner thinks the r= is.
>
> Regards,
>
> Dirk
>
John
=:->
signature.asc
Description: OpenPGP digital signature
Re: [PERFORM] performance decrease after reboot
On Tue, 19 Jul 2005, John Mendenhall wrote: > I tuned a query last week to obtain acceptable performance. > Here is my recorded explain analyze results: > > LOG: duration: 826.505 ms statement: explain analyze > [cut for brevity] > > I rebooted the database machine later that night. > Now, when I run the same query, I get the following > results: > > LOG: duration: 6931.701 ms statement: explain analyze > [cut for brevity] I just ran my query again, no changes from yesterday and it is back to normal: LOG: duration: 795.839 ms statement: explain analyze What could have been the problem? The major differences in the query plan are as follows: (1) The one that runs faster uses a Hash Join at the very top of the query plan. It does a Hash Cond on the country and code fields. (2) The one that runs slower uses a Materialize with the subplan, with no Hash items. The Materialize does Seq Scan of the countries table, and above it, a Join Filter is run. (3) The partners_pkey index on the partners table is in a different place in the query. Does anyone know what would cause the query plan to be different like this, for the same server, same query? I run vacuum analyze every night. Is this perhaps the problem? What setting do I need to tweak to make sure the faster plan is always found? Thanks for any pointers in this dilemma. JohnM -- John Mendenhall [EMAIL PROTECTED] surf utopia internet services ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Optimizer seems to be way off, why?
Richard Huxton wrote:
Dirk Lutzebäck wrote:
Hi,
I do not under stand the following explain output (pgsql 8.0.3):
explain analyze
select b.e from b, d
where b.r=516081780 and b.c=513652057 and b.e=d.e;
QUERY PLAN
Nested Loop (cost=0.00..1220.09 rows=1 width=4) (actual
time=0.213..2926.845 rows=324503 loops=1)
-> Index Scan using b_index on b (cost=0.00..1199.12 rows=1
width=4) (actual time=0.104..17.418 rows=3293 loops=1)
Index Cond: (r = 516081780::oid)
Filter: (c = 513652057::oid)
-> Index Scan using d_e_index on d (cost=0.00..19.22 rows=140
width=4) (actual time=0.009..0.380 rows=99 loops=3293)
Index Cond: ("outer".e = d.e)
Total runtime: 3638.783 ms
(7 rows)
Why is the rows estimate for b_index and the nested loop 1? It is
actually 3293 and 324503.
I'm guessing (and that's all it is) that b.r and b.c have a higher
correlation than the planner is expecting. That is, it expects the
b.c=... to reduce the number of matching rows much more than it is.
Try a query just on WHERE b.r=516081780 and see if it gets the estimate
right for that.
If it's a common query, it might be worth an index on (r,c)
--
Richard Huxton
Archonet Ltd
Thanks Richard, dropping the join for b.c now gives better estimates (it
also uses a different index now) although not accurate (off by factor
10). This query is embedded in a larger query which now got a 1000 times
speed up (!) because I can drop b.c because it is redundant.
Though, why can't the planner see this correlation? I think somebody
said the planner does not know about multiple column correlations, does it?
Regards,
Dirk
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Optimizer seems to be way off, why?
Dirk Lutzebäck wrote:
Hi,
I do not under stand the following explain output (pgsql 8.0.3):
explain analyze
select b.e from b, d
where b.r=516081780 and b.c=513652057 and b.e=d.e;
QUERY PLAN
Nested Loop (cost=0.00..1220.09 rows=1 width=4) (actual
time=0.213..2926.845 rows=324503 loops=1)
-> Index Scan using b_index on b (cost=0.00..1199.12 rows=1 width=4)
(actual time=0.104..17.418 rows=3293 loops=1)
Index Cond: (r = 516081780::oid)
Filter: (c = 513652057::oid)
-> Index Scan using d_e_index on d (cost=0.00..19.22 rows=140
width=4) (actual time=0.009..0.380 rows=99 loops=3293)
Index Cond: ("outer".e = d.e)
Total runtime: 3638.783 ms
(7 rows)
Why is the rows estimate for b_index and the nested loop 1? It is
actually 3293 and 324503.
I'm guessing (and that's all it is) that b.r and b.c have a higher
correlation than the planner is expecting. That is, it expects the
b.c=... to reduce the number of matching rows much more than it is.
Try a query just on WHERE b.r=516081780 and see if it gets the estimate
right for that.
If it's a common query, it might be worth an index on (r,c)
--
Richard Huxton
Archonet Ltd
---(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: [PERFORM] Impact of checkpoint_segments under continual load conditions
On 7/19/05, Christopher Petrilli <[EMAIL PROTECTED]> wrote: > It looks like the CVS HEAD is definately "better," but not by a huge > amount. The only difference is I wasn't run autovacuum in the > background (default settings), but I don't think this explains it. > Here's a graph of the differences and density of behavior: > > http://blog.amber.org/diagrams/pgsql_copy_803_cvs.png > > I can provide the raw data. Each COPY was 500 rows. Note that fsync > is turned off here. Maybe it'd be more stable with it turned on? I've updated this with trend-lines. Chris -- | Christopher Petrilli | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Impact of checkpoint_segments under continual load conditions
On 7/19/05, Christopher Petrilli <[EMAIL PROTECTED]> wrote: > On 7/19/05, Tom Lane <[EMAIL PROTECTED]> wrote: > > Christopher Petrilli <[EMAIL PROTECTED]> writes: > > > On 7/19/05, Tom Lane <[EMAIL PROTECTED]> wrote: > > >> How *exactly* are you invoking psql? > > > > > It is a subprocess of a Python process, driven using a pexpect > > > interchange. I send the COPY command, then wait for the '=#' to come > > > back. > > > > Some weird interaction with pexpect maybe? Try adding "-n" (disable > > readline) to the psql command switches. > > Um... WOW! > It doesn't stay QUITE that low, but it stays lower... quite a bit. > We'll see what happens over time. here's a look at the difference: http://blog.amber.org/diagrams/pgsql_readline_impact.png I'm running additional comparisons AFTER clustering and analyzing the tables... Chris -- | Christopher Petrilli | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Optimizer seems to be way off, why?
Hi,
I do not under stand the following explain output (pgsql 8.0.3):
explain analyze
select b.e from b, d
where b.r=516081780 and b.c=513652057 and b.e=d.e;
QUERY PLAN
Nested Loop (cost=0.00..1220.09 rows=1 width=4) (actual
time=0.213..2926.845 rows=324503 loops=1)
-> Index Scan using b_index on b (cost=0.00..1199.12 rows=1
width=4) (actual time=0.104..17.418 rows=3293 loops=1)
Index Cond: (r = 516081780::oid)
Filter: (c = 513652057::oid)
-> Index Scan using d_e_index on d (cost=0.00..19.22 rows=140
width=4) (actual time=0.009..0.380 rows=99 loops=3293)
Index Cond: ("outer".e = d.e)
Total runtime: 3638.783 ms
(7 rows)
Why is the rows estimate for b_index and the nested loop 1? It is
actually 3293 and 324503.
I did VACUUM ANALYZE before and I also increased the STATISTICS TARGET
on b.e to 500. No change.
Here is the size of the tables:
select count(oid) from b;
3532161
select count(oid) from b where r=516081780 and c=513652057;
3293
select count(oid) from d;
117270
Regards,
Dirk
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
