From: | "Dan Weber" <weberdan(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | forcing use of a specific (expression) index? |
Date: | 2007-05-02 20:18:32 |
Message-ID: | 5dfa06e10705021318t3743ab67s52a1dbc18616ca0c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I've got a table with about 15 columns and 200,000 rows. I have indexes on a
lot of my columns, but postgres doesn't seem to be grabbing the ideal index --
in this case, an expression index that exactly matches my WHERE clause.
I have the following query:
SELECT columns FROM my_table WHERE (bool_1 or int_1 = 0)
AND (int_2 IS NULL) AND (int_3 IS NULL) AND (protocol =
2) ORDER BY id LIMIT 1;
Here is an explain analyze:
Limit (cost=0.00..8.43 rows=1 width=111) (actual
time=17511.939..17511.940 rows=1 loops=1)
-> Index Scan using my_table_id_key on my_table
(cost=0.00..384000.58 rows=45562 width=111) (actual
time=17511.935..17511.935 rows=1 loops=1)
Filter: ((bool_1 OR (int_1 = 0)) AND (int_2
IS NULL) AND (int_3 IS NULL) AND (protocol = 2))
Total runtime: 17512.031 ms
I made an expression index specifically for that where clause:
CREATE INDEX special_testing_idx on my_table (((bool_1 or
int_1 = 0) AND (int_2 IS NULL) AND (int_3 IS
NULL) AND (protocol = 2)));
It looks like the database is letting the ORDER BY id LIMIT 1 override
the above index. I suppose I could live with that, except removing both
the ORDER and LIMIT clauses doesn't make it use that index.
Here is the EXPLAIN ANALYZE without the ORDER or LIMIT:
Bitmap Heap Scan on my_table (cost=1558.71..10154.01 rows=45562
width=111) (actual time=7442.835..14391.969 rows=678 loops=1)
Filter: ((bool_1 OR (int_1 = 0)) AND (int_2 IS
NULL) AND (int_3 IS NULL) AND (protocol = 2))
-> Bitmap Index Scan on my_table_int_2_null_idx
(cost=0.00..1558.71 rows=122487 width=0) (actual
time=7081.952..7081.952 rows=123001 loops=1)
Index Cond: ((int_2 IS NULL) = true)
Total runtime: 14392.966 ms
I've done lots of ANALYZEs of my_table, as well as tried setting
enable_seqscan to off.
Have I just created too many indexes? Is there some syntax I can use
to make the database recognize my big hairy WHERE clause? Would I be
better of making a special boolean column that resolves to that expression,
and then indexing that column?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-05-02 21:38:16 | Re: forcing use of a specific (expression) index? |
Previous Message | Stephen Harris | 2007-05-02 19:51:17 | Re: Have I b0rked something? Slow comparisons on "where x in (...)" |