Hi Tom,


David Teran <[EMAIL PROTECTED]> 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


---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

Reply via email to