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
