Re: Index usage on OR queries

On 31 Srpen 2011, 16:49, Andy Colson wrote:
> On 8/31/2011 9:35 AM, Tore Halvorsen wrote:
>> Hi,
>>
>> I'm trying to optimize a query where I have two tables that both have a
>> timestamp column. I want the result where either of the timestamps is
>> after a specified time. In a reduced form, like this:
>>
>>
>> CREATE TABLE a
>> (
>>    id serial NOT NULL PRIMARY KEY,
>>    time timestamp without time zone NOT NULL DEFAULT now()
>> );
>>
>> CREATE INDEX a_time_idx ON a USING btree (time DESC NULLS LAST);
>>
>> CREATE TABLE b
>> (
>>    id serial NOT NULL PRIMARY KEY,
>>    time timestamp without time zone NOT NULL DEFAULT now()
>> );
>>
>> CREATE INDEX b_time_idx ON b USING btree (time DESC NULLS LAST);
>>
>> --- generate some data
>> insert into a(time)
>> select now() - '10 year'::interval * random() from generate_series(1,
>> 1000000, 1);
>>
>> insert into b(time)
>> select now() - '10 year'::interval * random() from generate_series(1,
>> 1000000, 1);
>>
>> -- Using constraint works as expected, and uses the time index.
>> select * from a join b using(id)
>> where a.time >= '2011-08-15';
>>
>> -- ... both ways...
>> select * from a join b using(id)
>> where b.time >= '2011-08-15';
>>
>> -- However, if I'm trying to do this for both times at once, the time
>> index is not used at all
>> select * from a join b using(id)
>> where a.time >= '2011-08-15' OR b.time >= '2011-08-01'
>>
>> -- This can be optimized by using CTEs
>> with am as (
>>    select * from a where time >= '2011-08-15'
>> )
>> , bm as (
>>    select * from b where time >= '2011-08-15'
>> )
>> select * from am join bm using(id)
>>
>> -- end
>>
>> I'm just wondering why the optimizer does things the way it does - and
>> if the CTE version is the best way to go...
>>
>> The actual case is slightly more complex and uses more tables - this is
>> mostly a way to find updated data.
>>
>> --
>> Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
>> <demo> 2011 Tore Halvorsen || +052 0553034554
>
>
>
> On PG 9, after I ANALYZED the tables, it used indexes:
>
>
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------
>   Merge Join  (cost=1.59..82778.35 rows=13171 width=20) (actual
> time=0.066..1076.616 rows=12966 loops=1)
>     Merge Cond: (a.id = b.id)
>     Join Filter: ((a."time" >= '2011-08-15 00:00:00'::timestamp without
> time zone) OR (b."time" >= '2011-08-01 0
>     ->  Index Scan using a_pkey on a  (cost=0.00..31389.36 rows=1000000
> width=12) (actual time=0.007..204.856 ro
>     ->  Index Scan using b_pkey on b  (cost=0.00..31389.36 rows=1000000
> width=12) (actual time=0.006..224.189 ro
>
>
> ANALYZE is the magic.

Yes ;-) Who says we don't have a magical fairy dust?

Anyway you could try to postpone the join a bit - determine the IDs first
and then join. Something like this

WITH t AS (
  SELECT id FROM a WHERE time >= '2011-08-15'
  UNION
  SELECT id FROM b WHERE time >= '2011-08-15'
)
SELECT * FROM a JOIN b ON (a.id = b.id) WHERE id IN (SELECT id FROM t);

or something like that. It's not as clean as your query, but in some cases
it's faster.

Tomas