
> EXPLAIN
> tour=# explain analyze  select * from tours  where
>            ( operator_id in (2,3,4,5,7) and type_id = 4 ) or
>            ( operator_id = 8 and type_id = 3);
> NOTICE:  QUERY PLAN:
> 
> Index Scan using type_idx, type_idx, type_idx, type_idx, type_idx, type_idx on tours  (cost=>
> 0.00..12.25 rows=1 width=1091) (actual time=0.27..0.27 rows=0 loops=1)
> Total runtime: 0.44 msec

Actually this plan looks very strange to me. One would expect it to only use 
type_idx twice (in lack of a better index (type_id, operator_id)). 
Fetch all rows with type_id=4 and then filter the result on operator_id in (...), 
then do type_id=3 and filter operator_id=8.
Seems there is room for another performance improvement here :-)

Andreas

Tue, 14 May 2002 08:37:13 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: strange explain 
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
>> tour=# explain analyze  select * from tours  where
>> ( operator_id in (2,3,4,5,7) and type_id = 4 ) or
>> ( operator_id = 8 and type_id = 3);

> Actually this plan looks very strange to me. One would expect it to only use 
> type_idx twice (in lack of a better index (type_id, operator_id)). 
> Seems there is room for another performance improvement here :-)

Yeah, this demonstrates that reducing the quals to canonical form isn't
always the best thing to do.

Or maybe we could just look for duplicate indexqual conditions at the
end of the process?

			regards, tom lane

Tue, 14 May 2002 09:39:17 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: strange explain 