Skip site navigation (1) Skip section navigation (2)

Re: Optimization idea

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimization idea
Date: 2010-04-22 12:37:32
Message-ID: 4BD0430C.1050308@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)2ndQuadrant(dot)com   www.2ndQuadrant.us


In response to

Responses

pgsql-performance by date

Next:From: Merlin MoncureDate: 2010-04-22 14:11:59
Subject: Re: Replacing Cursors with Temporary Tables
Previous:From: Merlin MoncureDate: 2010-04-22 12:17:00
Subject: Re: Replacing Cursors with Temporary Tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group