Re: optimizing Postgres queries

Hi Tom,


> David Teran <david.teran@cluster9.com> writes:
>> What we found out now is that a query with a single 'where' works
>> fine,
>> the query planer uses the index but when we have 'two' where clauses
>> it
>> does not use the index anymore:
>
>> EXPLAIN ANALYZE SELECT columns...  FROM "KEY_VALUE_META_DATA" t0 WHERE
>> (t0."ID_VALUE" = 14542); performs fine, less than one millisecond.
>
>> EXPLAIN ANALYZE SELECT columns...  FROM "KEY_VALUE_META_DATA" t0 WHERE
>> (t0."ID_VALUE" = 14542 OR t0."ID_VALUE" = 14550); performs bad: about
>> 235 milliseconds.
>
> Please, when you ask this sort of question, show the EXPLAIN ANALYZE
> output.  It is not a virtue to provide minimal information and see if
> anyone can guess what's happening.
>
Sorry for that, i thought this is such a trivial question that the
answer is easy.

explain result from first query:

Index Scan using key_value_meta_data__id_value__fk_index on "KEY_VALUE_M
ETA_DATA" t0  (cost=0.00..1585.52 rows=467 width=1068) (actual time=0.42
4..0.493 rows=13 loops=1)

  Index Cond: ("ID_VALUE" = 21094)

Total runtime: 0.608 ms



explain result from second query:

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))

Total runtime: 172.354 ms



I found out that its possible to disable seq scans with set
enable_seqscan to off; then the second query result looks like this:

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..3173.
35 rows=931 width=1068) (actual time=0.116..0.578 rows=25 loops=1)

  Index Cond: (("ID_VALUE" = 21094) OR ("ID_VALUE" = 21103))

Total runtime: 0.716 ms


But i read in the docs that its not OK to turn this off by default. I
really wonder if this is my fault or not, from my point of view this is
such a simple select that the query plan should not result in a table
scan.

Regards David