Re: optimizing Postgres queries

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