From: Csaba Nagy <nagy@ecircle-ag.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Searching for the cause of a bad plan
Date: Fri, 21 Sep 2007 16:26:39 +0200
Message-ID: <1190384799.4661.217.camel@PCD12478> (raw)
In-Reply-To: <1190381777.4202.50.camel@ebony.site>
References: <1190369024.4661.173.camel@PCD12478>
<1190372365.4202.14.camel@ebony.site>
<1190374166.4661.194.camel@PCD12478>
<1190374478.4202.20.camel@ebony.site>
<1190376744.4661.208.camel@PCD12478>
<1190381777.4202.50.camel@ebony.site>
[snip]
Ok, I was not able to follow your explanation, it's too deep for me into
what the planner does...
> Incidentally, the way out of this is to improve the stats by setting
> stats target = 1000 on column a of ta. That will allow the optimizer to
> have a better estimate of the tail of the distribution of a, which
> should then be more sensibly reflected in the cost of the Index Scan.
> That doesn't solve the actual problem, but should help in your case.
OK, I can confirm that. I set the statistics target for column "a" on
table_a to 1000, analyzed, and got the plan below. The only downside is
that analyze became quite expensive on table_a, it took 15 minutes and
touched half of the pages... I will experiment with lower settings,
maybe it will work with less than 1000 too.
db> explain analyze execute test_001(31855344);
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..4499.10 rows=10 width=1804) (actual
time=103.566..120.363 rows=2 loops=1)
-> Nested Loop (cost=0.00..344630.97 rows=766 width=1804) (actual
time=103.563..120.359 rows=2 loops=1)
-> Index Scan using pk_table_a on table_a ta
(cost=0.00..67097.97 rows=78772 width=16) (actual time=71.965..77.284
rows=2 loops=1)
Index Cond: (a = $1)
-> Index Scan using pk_table_b2 on table_b2 tb
(cost=0.00..3.51 rows=1 width=1788) (actual time=21.526..21.528 rows=1
loops=2)
Index Cond: (ta.b = tb.b)
Total runtime: 120.584 ms
Thanks,
Csaba.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nagy@ecircle-ag.com, simon@2ndquadrant.com
Subject: Re: Searching for the cause of a bad plan
In-Reply-To: <1190384799.4661.217.camel@PCD12478>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox