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

Re: possible wrong query plan on pg 8.3.5,

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: zz_11(at)mail(dot)bg
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: possible wrong query plan on pg 8.3.5,
Date: 2009-09-13 19:21:31
Message-ID: 9029.1252869691@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
zz_11(at)mail(dot)bg writes:
> I am running a relativ complex query on pg 8.3.5 and have (possible)  
> wrong query plan.
> ...
> If I run the query without thle last part : and n.num like '191%'     
> it work ok as speed ~ 30 sec on not very big db.
> If I run the full query it take very long time to go ( i never waited  
> to the end but it take  > 60 min.)

I'm betting that it's badly underestimating the number of rows
satisfying the LIKE condition:

>                                               ->  Index Scan using  
> i_nomen_num on a_nomen n  (cost=0.00..56.39 rows=24 width=128)
>                                                     Index Cond:  
> (((num)::text >= '191'::text) AND ((num)::text < '192'::text))
>                                                     Filter:  
> ((num)::text ~~ '191%'::text)

Is 24 the right number of rows for that, or anywhere close?  If not, try
raising the statistics target for this table.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Heikki LinnakangasDate: 2009-09-13 19:40:42
Subject: Re: Persistent Plan Cache
Previous:From: Joshua RubinDate: 2009-09-13 18:15:04
Subject: Re: Persistent Plan Cache

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