Обсуждение: forcing use of a specific (expression) index?

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?

"Dan Weber" <weberdan@gmail.com> writes:
> 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;

> 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)));

A partial index would be WAY more useful than that:

create index on my_table(id) where (bool_1 or int_1 = 0) ...

            regards, tom lane

Dan Weber wrote:
> 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)));

No, you haven't. What you've done here is create an index *for that
expression*. And it's not terribly useful because your expression will
only have two possible values: true, false.

What you were after is something like:

CREATE INDEX special_idx2 ON my_table (id) WHERE (...long expression...)

--
   Richard Huxton
   Archonet Ltd

Thanks to you and Tom.  The partial index solution is working splendidly.

On 5/3/07, Richard Huxton <dev@archonet.com> wrote:
> Dan Weber wrote:
> > 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)));
>
> No, you haven't. What you've done here is create an index *for that
> expression*. And it's not terribly useful because your expression will
> only have two possible values: true, false.
>
> What you were after is something like:
>
> CREATE INDEX special_idx2 ON my_table (id) WHERE (...long expression...)
>
> --
>    Richard Huxton
>    Archonet Ltd
>