[PERFORM] Different query plans for the same query

2009-09-18 Thread Hell, Robert 
Hi all,

on our PostgreSQL 8.3.1 (CentOS 5.3 64-bit) two different query plans
for one of our (weird) queries are generated. One of the query plans
seems to be good (and is used most of the time). The other one is bad -
the query takes about 2 minutes and the database process, which is
executing the query, is cpu bound during this time.

After several tries I was able to reproduce the problem when executing
the query with EXPLAIN ANALYZE. The bad query plan was generated only
seconds after the good one was used when executing the query. What's the
reasond for the different query plans? Statistics are up to date.

Good:

EXPLAIN ANALYZE SELECT DISTINCT t6.objid
  FROM ataggval q1_1,
   atobjval t6,
   atobjval t7,
   atobjval t8,
   atobjval t9,
   cooobject t10
 WHERE q1_1.objid = t6.objid AND
   q1_1.attrid = 285774255985993 AND
   q1_1.aggrid = 0 AND
   t6.aggrid = q1_1.aggval AND
   t7.aggrid = 0 AND
   t7.objid = t6.objid AND
   t8.aggrid = 0 AND
   t8.objid = t6.objid AND
   t9.aggrid = 0 AND
   t9.objid = t6.objid AND
   t10.objid = t6.objid AND
   t6.objval = 285774255985589 AND
   t6.attrid=285774255985991 AND
   t7.objval = 625445988202446985 AND
   t7.attrid=285774255985855 AND
   t8.objval = 625445988286355913 AND
   t8.attrid=285774255985935 AND
   t9.objval = 625445988269570350 AND
   t9.attrid=285774255985938 AND
   t10.objclassid = 285774255985894  ORDER BY t6.objid;
 
QUERY PLAN



 Unique  (cost=66.58..66.59 rows=1 width=8) (actual
time=1548.207..1548.208 rows=1 loops=1)
   ->  Sort  (cost=66.58..66.58 rows=1 width=8) (actual
time=1548.206..1548.207 rows=1 loops=1)
 Sort Key: t6.objid
 Sort Method:  quicksort  Memory: 25kB
 ->  Nested Loop  (cost=0.00..66.57 rows=1 width=8) (actual
time=1044.759..1548.190 rows=1 loops=1)
   Join Filter: (t6.objid = t7.objid)
   ->  Nested Loop  (cost=0.00..54.52 rows=1 width=40)
(actual time=21.938..1541.633 rows=350 loops=1)
 Join Filter: (t6.objid = t8.objid)
 ->  Nested Loop  (cost=0.00..42.47 rows=1 width=32)
(actual time=21.907..1422.710 rows=364 loops=1)
   Join Filter: (t6.objid = t9.objid)
   ->  Nested Loop  (cost=0.00..30.42 rows=1
width=24) (actual time=0.151..920.873 rows=775 loops=1)
 ->  Nested Loop  (cost=0.00..21.97
rows=1 width=16) (actual time=0.065..915.387 rows=775 loops=1)
   Join Filter: (q1_1.objid =
t6.objid)
   ->  Index Scan using ind_atobjval
on atobjval t6  (cost=0.00..12.04 rows=1 width=12) (actual
time=0.031..0.863 rows=775 loops=1)
 Index Cond: ((attrid =
285774255985991::bigint) AND (objval = 285774255985589::bigint))
   ->  Index Scan using ind_ataggval
on ataggval q1_1  (cost=0.00..9.92 rows=1 width=12) (actual
time=0.006..0.897 rows=1243 loops=775)
 Index Cond: ((q1_1.attrid =
285774255985993::bigint) AND (q1_1.aggval = t6.aggrid))
 Filter: (q1_1.aggrid = 0)
 ->  Index Scan using cooobjectix on
cooobject t10  (cost=0.00..8.44 rows=1 width=8) (actual
time=0.005..0.006 rows=1 loops=775)
   Index Cond: (t10.objid =
t6.objid)
   Filter: (t10.objclassid =
285774255985894::bigint)
   ->  Index Scan using ind_atobjval on atobjval
t9  (cost=0.00..12.04 rows=1 width=8) (actual time=0.007..0.490 rows=694
loops=775)
 Index Cond: ((t9.attrid =
285774255985938::bigint) AND (t9.objval = 625445988269570350::bigint))
 Filter: (t9.aggrid = 0)
 ->  Index Scan using ind_atobjval on atobjval t8
(cost=0.00..12.04 rows=1 width=8) (actual time=0.007..0.248 rows=350
loops=364)
   Index Cond: ((t8.attrid =
285774255985935::bigint) AND (t8.objval = 625445988286355913::bigint))
   Filter: (t8.aggrid = 0)
   ->  Index Scan using ind_atobjval on atobjval t7
(cost=0.00..12.04 rows=1 width=8) (actual time=0.005..0.015 rows=13
loops=350)
 Index Cond: ((t7.attrid = 285774255985855::bigint)
AND (t7.objval = 625445988202446985::bigint))
 Filter: (t7.aggrid = 0)
 Total runtime: 1548.339 ms
(31 rows)


Bad: 
EXPLAIN ANALYZE SELECT DISTINCT t6.objid
  FROM ataggval q1_1,
   atobjval t6,
   atobjval t7,
   atobjval t8,
   atobjval t9,
   cooobject t10
 WHERE q1_1.objid =

Re: [PERFORM] Different query plans for the same query

2009-09-18 Thread tv 
> Hi all,
>
> on our PostgreSQL 8.3.1 (CentOS 5.3 64-bit) two different query plans
> for one of our (weird) queries are generated. One of the query plans
> seems to be good (and is used most of the time). The other one is bad -
> the query takes about 2 minutes and the database process, which is
> executing the query, is cpu bound during this time.
>
> After several tries I was able to reproduce the problem when executing
> the query with EXPLAIN ANALYZE. The bad query plan was generated only
> seconds after the good one was used when executing the query. What's the
> reasond for the different query plans? Statistics are up to date.
>
> ...

Hi,

please, when posting an explain plan, either save it into a file and
provide a URL (attachments are not allowed here), or use
explain.depesz.com or something like that. This wrapping makes the plan
unreadable so it's much more difficult to help you.

I've used the explain.depesz.com (this time):

- good plan: http://explain.depesz.com/s/HX
- bad plan: http://explain.depesz.com/s/gcr

It seems the whole problem is caused by the 'Index Scan using ind_atobjval
on atobjval t9' - in the first case it's executed only 775x, but in the
second case it's moved to the nested loop (one level deeper) and suddenly
it's executed 271250x. And that causes the huge increase in cost.

Why is this happening? I'm not sure, but I'm not quite sure the statistics
are up to data and precise enough - some of the steps state 'rows=1'
estimate, but 'rows=775' in the actual results.

Have you tried to increase target on the tables? That might provide more
accurate stats, thus better estimates.

regards
Tomas


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Different query plans for the same query

2009-09-18 Thread Hell, Robert 
Hi,

sorry about that.

We use 100 as default_statistics_target for this database. The default should 
be 10 here - statistics are up to date I executed analyze manually this morning.

As mentioned before, the "bad plan" only happens once or twice a day - so the 
reproduction of that plan is very difficult. 

I now played a little bit with statistics target for those three tables (alter 
table ...set statistics). It seems that there is a better query plan than the 
good one when using 10 as statistics target.

bad plan (sometimes with statistcs target 100, seconds after the good plan was 
chosen) - about 2 minutes: http://explain.depesz.com/s/gcr
good plan (most of the time with statistcs target 100) - about one second: 
http://explain.depesz.com/s/HX
very good plan (with statistics target 10) - about 15 ms: 
http://explain.depesz.com/s/qMc

What's the reason for that? I always thought increasing default statistics 
target should make statistics (and query plans) better.

Regards,
Robert

-Ursprüngliche Nachricht-
Von: t...@fuzzy.cz [mailto:t...@fuzzy.cz] 
Gesendet: Freitag, 18. September 2009 11:20
An: Hell, Robert
Cc: pgsql-performance@postgresql.org
Betreff: Re: [PERFORM] Different query plans for the same query

> Hi all,
>
> on our PostgreSQL 8.3.1 (CentOS 5.3 64-bit) two different query plans
> for one of our (weird) queries are generated. One of the query plans
> seems to be good (and is used most of the time). The other one is bad -
> the query takes about 2 minutes and the database process, which is
> executing the query, is cpu bound during this time.
>
> After several tries I was able to reproduce the problem when executing
> the query with EXPLAIN ANALYZE. The bad query plan was generated only
> seconds after the good one was used when executing the query. What's the
> reasond for the different query plans? Statistics are up to date.
>
> ...

Hi,

please, when posting an explain plan, either save it into a file and
provide a URL (attachments are not allowed here), or use
explain.depesz.com or something like that. This wrapping makes the plan
unreadable so it's much more difficult to help you.

I've used the explain.depesz.com (this time):

- good plan: http://explain.depesz.com/s/HX
- bad plan: http://explain.depesz.com/s/gcr

It seems the whole problem is caused by the 'Index Scan using ind_atobjval
on atobjval t9' - in the first case it's executed only 775x, but in the
second case it's moved to the nested loop (one level deeper) and suddenly
it's executed 271250x. And that causes the huge increase in cost.

Why is this happening? I'm not sure, but I'm not quite sure the statistics
are up to data and precise enough - some of the steps state 'rows=1'
estimate, but 'rows=775' in the actual results.

Have you tried to increase target on the tables? That might provide more
accurate stats, thus better estimates.

regards
Tomas


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Database performance post-VACUUM FULL

2009-09-18 Thread Karl Wright 

Hi all,

We're using Postgresql 8.3.7 on Debian.  We are seeing a very strange performance situation with our application which I am 
hoping that someone can shed light on.


Our tests find that our application runs quite well on 8.3.7 initially.  The test consists of database creation followed by 30 
cycles of creation and removal of approximately 1,000,000 rows (across all tables) per cycle.  However, when database 
maintenance takes place (which consists of a VACUUM FULL operation, and some table REINDEX operations), subsequent cycle 
performance is more than 2x worse.  What's more, after one VACUUM FULL operation has been done on the database, no subsequent 
VACUUM FULL operations *ever* seem to restore it to proper performance levels.


We used the same general maintenance procedure with 8.2 and found that it worked as expected, so we were quite surprised to 
discover this problem with 8.3.7.  Anybody know what's going on?


Thanks,
Karl

--
Karl Wright
Software Engineer

MetaCarta, Inc.
350 Massachusetts Avenue, 4th Floor, Cambridge, MA 02139 USA

(617)-301-5511

www.metacarta.com 
Where to find it.

This message may contain privileged, proprietary, and otherwise private
information. If you are not the intended recipient, please notify the
sender immediately.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Database performance post-VACUUM FULL

2009-09-18 Thread Robert Haas 
On Fri, Sep 18, 2009 at 8:44 AM, Karl Wright  wrote:
> Hi all,
>
> We're using Postgresql 8.3.7 on Debian.  We are seeing a very strange
> performance situation with our application which I am hoping that someone
> can shed light on.
>
> Our tests find that our application runs quite well on 8.3.7 initially.  The
> test consists of database creation followed by 30 cycles of creation and
> removal of approximately 1,000,000 rows (across all tables) per cycle.
>  However, when database maintenance takes place (which consists of a VACUUM
> FULL operation, and some table REINDEX operations), subsequent cycle
> performance is more than 2x worse.  What's more, after one VACUUM FULL
> operation has been done on the database, no subsequent VACUUM FULL
> operations *ever* seem to restore it to proper performance levels.
>
> We used the same general maintenance procedure with 8.2 and found that it
> worked as expected, so we were quite surprised to discover this problem with
> 8.3.7.  Anybody know what's going on?

Can you post to the list all the uncommented settings from your
postgresql.conf, the output of VACUUM VERBOSE, and the output of
EXPLAIN ANALYZE for some representative queries?

...Robert

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Use of BETWEEN with identical values

2009-09-18 Thread Robert Haas 
On Thu, Sep 17, 2009 at 5:02 PM, André Volpato
 wrote:
> André Volpato escreveu:
>>
>> (...)
>>
>> (Postgres 8.3.6, Debian Linux 2.6.18-6-amd64)
>>
>> (...)
>
>> Condition 1:
>> # select fat_referencia from bds_contratacao_fatura where fat_referencia
>> BETWEEN 200908 AND 200908;
>> Index Scan using ibds_contratacao_fatura1 on bds_contratacao_fatura
>>  (cost=0.00..5.64 rows=1 width=4) (actual time=0.023..79.952 rows=163689
>> loops=1)
>>  Index Cond: ((fat_referencia >= 200908) AND (fat_referencia <= 200908))
>> Total runtime: 110.470 ms
>
>> Condition 3:
>> # select fat_referencia from bds_contratacao_fatura where fat_referencia =
>> 200908;
>> Index Scan using ibds_contratacao_fatura1 on bds_contratacao_fatura
>>  (cost=0.00..4745.07 rows=142940 width=4) (actual time=0.022..77.818
>> rows=163689 loops=1)
>>  Index Cond: (fat_referencia = 200908)
>> Total runtime: 108.292 ms
>>
>> I expect Postgres would give me the same plan in conditions 1 and 3.
>
> And also the core team...
>
> This behaviour is 8.3 related. In 8.4, conditions 1 and 3 results in the
> same plan.

Hmm.  I don't see anything in the release notes about it, but it's not
surprising that the optimizer would be improved in a newer version.

...Robert

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Different query plans for the same query

2009-09-18 Thread Tom Lane 
"Hell, Robert"  writes:
> bad plan (sometimes with statistcs target 100, seconds after the good plan 
> was chosen) - about 2 minutes: http://explain.depesz.com/s/gcr
> good plan (most of the time with statistcs target 100) - about one second: 
> http://explain.depesz.com/s/HX
> very good plan (with statistics target 10) - about 15 ms: 
> http://explain.depesz.com/s/qMc

> What's the reason for that?

Garbage in, garbage out :-(.  When you've got rowcount estimates that
are off by a couple orders of magnitude, it's unsurprising that you get
bad plan choices.  In this case it appears that the "bad" and "good"
plans have just about the same estimated cost.  I'm guessing that the
underlying statistics change a bit due to autovacuum activity, causing
the plan choice to flip unexpectedly.

The real fix would be to get the rowcount estimates more in line with
reality.  I think the main problem is that in cases like

  -> Index Scan using ind_atobjval on atobjval t6 (cost=0.00..12.04 rows=1 
width=12) (actual time=0.032..0.953 rows=775 loops=1)
   Index Cond: ((attrid = 285774255985991::bigint) AND (objval = 
285774255985589::bigint))

the planner is supposing that the two conditions are independent when
they are not.  Is there any way you can refactor the data representation
to remove the hidden redundancy?

regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Different query plans for the same query

2009-09-18 Thread Robert Haas 
2009/9/18  :
>> Hi all,
>>
>> on our PostgreSQL 8.3.1 (CentOS 5.3 64-bit) two different query plans
>> for one of our (weird) queries are generated. One of the query plans
>> seems to be good (and is used most of the time). The other one is bad -
>> the query takes about 2 minutes and the database process, which is
>> executing the query, is cpu bound during this time.
>>
>> After several tries I was able to reproduce the problem when executing
>> the query with EXPLAIN ANALYZE. The bad query plan was generated only
>> seconds after the good one was used when executing the query. What's the
>> reasond for the different query plans? Statistics are up to date.
>>
>> ...
>
> Hi,
>
> please, when posting an explain plan, either save it into a file and
> provide a URL (attachments are not allowed here), or use
> explain.depesz.com or something like that. This wrapping makes the plan
> unreadable so it's much more difficult to help you.

Uh, since when are attachments not allowed here?  I completely agree
that line-wrapping is BAD, but I don't agree that pastebin is good.  I
would much rather have the relevant material in the email, or in an
attachment, than in some other web site that may or may not format it
readably, may or may not be easy to cut and paste, and will definitely
not become part of our archives.

...Robert

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance