David Teran <[EMAIL PROTECTED]> writes:Sorry for that, i thought this is such a trivial question that the answer is easy.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.
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
