Re: How index are running and how to optimise ?

Hervé Piedvache wrote:

>Hi,
>
>I have may be a stupid question, but I'm a little surprised with some explains
>I have, using date fields ...
>
>I would like to understand exactly when index are used ...
>I'm using PostgresQL 7.4.1
>
>I have a table with 351 000 records.
>I have about 300 to 600 new records by day
>I have an index like this :
>ix_contracts_start_stop_date btree (start_date, stop_date)
>
>I want to simply do something like this :
>
>select o.id_contract
> from contracts o
> where o.start_date <= '2001-10-31'
> and (o.stop_date > '2001-11-06' or stop_date is null);
>
>OK I get an explain like this :
> QUERY PLAN
>--------------------------------------------------------------------------------------------------------------
> Seq Scan on contracts o (cost=0.00..12021.80 rows=160823 width=4)
> Filter: ((start_date <= '2001-10-31'::date) AND ((stop_date >
>'2001-11-06'::date) OR (stop_date IS NULL)))
>
>I understand that the OR could make the no use of the stop_date index ..., but
>why I'm not using the index for the start_date part ?
>
>Index are used only if I use an egality like this :
>
>select o.id_contract
> from contracts o
> where o.start_date = '2001-10-31'
> and o.stop_date = '2001-11-06';
>
> QUERY PLAN
>------------------------------------------------------------------------------------------------
> Index Scan using ix_contracts_start_stop_date on contracts o
>(cost=0.00..6.00 rows=1 width=4)
> Index Cond: ((start_date = '2001-10-31'::date) AND (stop_date =
>'2001-11-06'::date))
>
>Could you please explain me why index are not used with <, > and how I can
>optimise my request ... I have no idea but I'm using this request to do
>insert in another table and this segmentation take 13 hours for making the
>insert ! :o((
>
>Thanks for help,
>
>
Have you ANALYZEd recently? If not you need to do that regularly. Try

VACUUM ANALYZE contracts;

to vacuum that specific table.

Could you also try

select
o.id_contract
from
contracts o
where
o.start_date NOT BETWEEN '2001-10-31' AND '2001-11-06' OR
o.stop_date IS NULL;

Also could you paste the results of EXPLAIN ANALYZE instead of EXPLAIN.

Cheers

Nick