Re: Date index not used when selecting a date range

On 9:09 am 07/28/08 Poul Møller Hansen  <freebsd@pbnet.dk> wrote:
> But when selecting a date range I get this
> explain SELECT * FROM public.stat WHERE node = '1010101010' AND
>  ("date" <= '2008-06-30'::date AND "date" >= '2008-01-01'::date)
>
> "Bitmap Heap Scan on stat  (cost=710.14..179319.44 rows=39174
> width=146)" "  Recheck Cond: ((node)::text = '1010101010'::text)"
> "  Filter: ((date <= '2008-06-30'::date) AND (date >=
> '2008-01-01'::date))" "  ->  Bitmap Index Scan on idx_stat_node_id
> (cost=0.00..710.14 rows=55182 width=0)"
> "        Index Cond: ((node)::text = '1010101010'::text)"

You may want to do an explain analyze on the query. That would help others
help you.

Have you run analyze on the table?
How selective is the condition node = '1010101010' and the date range. In
particular, do you have an idea what percentange of the table fits into
that date range?

What about the "type" column? You have it in the index, but not in your
query. Have you tried adding type to the query? Will that make it more
selective?