From: | David Teran <david(dot)teran(at)cluster9(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Shridhar Daithankar <shridhar_daithankar(at)myrealbox(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: optimizing Postgres queries |
Date: | 2004-01-05 19:02:01 |
Message-ID: | A5055773-3FB1-11D8-A528-000A95A6F0DC@cluster9.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi Tom,
> David Teran <david(dot)teran(at)cluster9(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | John Siracusa | 2004-01-05 19:03:12 | Select max(foo) and select count(*) optimization |
Previous Message | Vivek Khera | 2004-01-05 19:02:00 | Re: deferred foreign keys |