From: Greg Smith <greg@2ndquadrant.com>
To: Vlad Arkhipov <arhipov@dc.baikal.ru>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimization idea
Date: Thu, 22 Apr 2010 08:37:32 -0400
Message-ID: <4BD0430C.1050308@2ndquadrant.com> (raw)
In-Reply-To: <4BD0161A.4010908@dc.baikal.ru>
References: <4BD0161A.4010908@dc.baikal.ru>
Vlad Arkhipov wrote:
> Please do this small optimization if it is possible. It seem that the
> optimizer have the all information to create a fast plan but it does
> not do that.
This isn't strictly an optimization problem; it's an issue with
statistics the optimizer has to work with, the ones ANALYZE computes.
You noticed this yourself:
> HERE IS THE PROBLEM. IF THE ESTIMATED COUNT = 1 OPTIMIZER BUILDS THE
> CORRECT FAST PLAN, BUT IF THE ESTIMATION IS GREATER THAN 1 WE HAVE A
> PROBLEM
See http://www.postgresql.org/docs/current/static/planner-stats.html for
an intro to this area.
You didn't mention your PostgreSQL version. If you're running 8.3 or
earlier, an increase to default_statistics_target might be in order to
get more data about the distribution of data in the table, to reduce the
odds of what you're seeing happening.
I can't replicate your problem on the current development 9.0; all three
plans come back with results quickly when I just tried it:
Nested Loop (cost=0.00..50.76 rows=204 width=32) (actual
time=0.049..0.959 rows=200 loops=1)
-> Seq Scan on t1 (cost=0.00..1.06 rows=1 width=16) (actual
time=0.013..0.016 rows=1 loops=1)
Filter: (t = 2)
-> Index Scan using t_idx on t2 (cost=0.00..47.66 rows=204
width=16) (actual time=0.029..0.352 rows=200 loops=1)
Index Cond: (t2.t = 2)
Total runtime: 1.295 ms
Nested Loop (cost=0.00..1042.77 rows=20020 width=32) (actual
time=0.042..0.437 rows=100 loops=1)
-> Seq Scan on t1 (cost=0.00..1.06 rows=1 width=16) (actual
time=0.013..0.015 rows=1 loops=1)
Filter: (id = 3)
-> Index Scan using t_idx on t2 (cost=0.00..791.45 rows=20020
width=16) (actual time=0.022..0.164 rows=100 loops=1)
Index Cond: (t2.t = t1.t)
Total runtime: 0.608 ms
Bitmap Heap Scan on t2 (cost=16.11..558.73 rows=433 width=16) (actual
time=0.095..0.674 rows=400 loops=1)
Recheck Cond: (t = ANY ('{2,3,4}'::bigint[]))
-> Bitmap Index Scan on t_idx (cost=0.00..16.00 rows=433 width=0)
(actual time=0.075..0.075 rows=400 loops=1)
Index Cond: (t = ANY ('{2,3,4}'::bigint[]))
Total runtime: 1.213 ms
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
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: greg@2ndquadrant.com, arhipov@dc.baikal.ru
Subject: Re: Optimization idea
In-Reply-To: <4BD0430C.1050308@2ndquadrant.com>
* 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