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

Re: optimizing Postgres queries

From: David Teran <david(dot)teran(at)cluster9(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: optimizing Postgres queries
Date: 2004-01-05 19:20:49
Message-ID: 4530AF9E-3FB4-11D8-A528-000A95A6F0DC@cluster9.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Tom,

first of all thanks for your help! I really appreciate your fast  
response and if you ever have a question about WebObjects, just drop me  
line ;-)

>> Seq Scan on "KEY_VALUE_META_DATA" t0  (cost=0.00..2671.16 rows=931  
>> width
>> =1068) (actual time=122.669..172.179 rows=25 loops=1)
>>   Filter: (("ID_VALUE" = 21094) OR ("ID_VALUE" = 21103))
>
> The problem is evidently that the row estimate is so far off (931
> estimate vs 25 actual).  Have you done ANALYZE or VACUUM ANALYZE
> on this table recently?  If you have, I'd be interested to see the
> pg_stats row for ID_VALUE.  It might be that you need to increase
> the statistics target for this table.
>
I am absolutely new to PostgreSQL. OK, after VACUUM ANALYZE i get:

Index Scan using key_value_meta_data__id_value__fk_index, key_value_meta 
_data__id_value__fk_index on "KEY_VALUE_META_DATA" t0  (cost=0.00..19.94 
 rows=14 width=75) (actual time=0.615..1.017 rows=25 loops=1)
  Index Cond: (("ID_VALUE" = 21094) OR ("ID_VALUE" = 21103))
Total runtime: 2.565 ms

and the second time i invoke this i get


Index Scan using key_value_meta_data__id_value__fk_index, key_value_meta 
_data__id_value__fk_index on "KEY_VALUE_META_DATA" t0  (cost=0.00..19.94 
 rows=14 width=75) (actual time=0.112..0.296 rows=25 loops=1)
  Index Cond: (("ID_VALUE" = 21094) OR ("ID_VALUE" = 21103))
Total runtime: 0.429 ms

Much better. So i think i will first read more about this optimization  
stuff and regular maintenance things. This is something i like very  
much from FrontBase: no need for such things, simply start and run. But  
other things were not so fine ;-).

Is there any hint where to start to understand more about this  
optimization problem?

regards David

		

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2004-01-05 19:23:45
Subject: Re: optimizing Postgres queries
Previous:From: Tom LaneDate: 2004-01-05 19:05:48
Subject: Re: optimizing Postgres queries

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