Re: planer don't use index. bad plan for where id = x or id in (select ...)

Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:

> Hello,
>
> Pg make query 1. and 2. very fast (use index), but for query 3. dont use
> index. I can solve its using select union, but I readed so pg 7.5 don't
> problem with OR operator. I use cvs pg. I used vacuum analyze first.

I don't think even in CVS that there's much hope on this query with the OR
between the IN clause and a plain equals clause, but there are a few
improvements you could make to the UNION form:

> intra=# explain ANALYZE select * from sp_op_uct where sp_op_id in (select
> sp_op_id from sp_op_produkt where bal_zak = 34123) union select * from
> sp_op_uct where sp_op_id = 34123;

Try

SELECT *
  FROM sp_op_uct
 WHERE sp_op_id IN (
         SELECT sp_op_id
           FROM sp_op_produkt
          WHERE bal_zak = 34123
         UNION ALL
         SELECT 34123 AS sp_op_id
       )

You can select just the plain constant 34123 instead of having to fetch the
row from sp_op_uct. This probably won't save much actual time since the record
would have been in cache anyways. And you can use UNION ALL instead of UNION
since the IN will eliminate duplicates anyways. This avoids an extra
sort/uniquify step. Again it wouldn't really save much time since the extra
sort was only across 2 rows. But I like seeing simple clean plans even if they
aren't really much faster.

--
greg