Обсуждение: Date index not used when selecting a date range

I'm wondering why this index is not used for my query.

This is the index:
CREATE INDEX idx_stat_date_node_type
  ON public.stat
  USING btree
  (date, node, "type");

When quering an exact date, it is used

explain SELECT * FROM public.stat WHERE node = '1010101010' AND date =
'2008-01-01'

"Index Scan using idx_stat_date_node_type on stat  (cost=0.00..279.38
rows=150 width=146)"
"  Index Cond: ((date = '2008-01-01'::date) AND ((node)::text =
'1010101010'::text))"


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)"


How can I change my query so it will use the index ?

Thanks
Poul

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?

> You can prevent postgres from using the index on node by changing the reference in the WHERE clause to an expression,
likeso: 
>
> SELECT * FROM public.stat WHERE node||'' = '1010101010' AND
>  ("date" <= '2008-06-30'::date AND "date" >= '2008-01-01'::date);
>
> Perhaps this will lead the optimizer to choose the index on date. However, I have noticed reluctance in the postgres
optimizerto use multi-column indexes, presumably because the increased size of the indexed values lowers expectations
forperformance of the index. 
>
>
>
I think you are right about the multi-column usage.
When I use node||'' instead of node, it will do a seq scan.

Poul

>> This is the index:
>> CREATE INDEX idx_stat_date_node_type
>>  ON public.stat
>>  USING btree
>>  (date, node, "type");
>>
>>
>> explain SELECT * FROM public.stat WHERE node = '1010101010' AND
>> ("date" <= '2008-06-30'::date AND "date" >= '2008-01-01'::date)
>>
>
> Try changing the index order to node, date rather than date, node. You
> need the column on which you'll be doing range checking to be the
> furthest to the right in the index column list.
>
>
Then it works. Unfortunately the production database is always in use
and it contains more than 100 mill. rows,
so creating an index is not an option.

Poul

> Have you run analyze on the table?
>
Yes
> 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?
>
There are around 1000 nodes and there is data for two years, so it must
be around 1/4000 of all rows
> 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?
>
>
Why haven't I tried that ... That did the trick, thanks!

Poul

On Tue, Jul 29, 2008 at 1:25 AM, Poul Møller Hansen <freebsd@pbnet.dk> wrote:
>
>>> This is the index:
>>> CREATE INDEX idx_stat_date_node_type
>>>  ON public.stat
>>>  USING btree
>>>  (date, node, "type");
>>>
>>>
>>> explain SELECT * FROM public.stat WHERE node = '1010101010' AND
>>> ("date" <= '2008-06-30'::date AND "date" >= '2008-01-01'::date)
>>>
>>
>> Try changing the index order to node, date rather than date, node. You
>> need the column on which you'll be doing range checking to be the
>> furthest to the right in the index column list.
>>
>>
>
> Then it works. Unfortunately the production database is always in use and it
> contains more than 100 mill. rows,
> so creating an index is not an option.

If you're running 8.3 you can create index concurrently.