From: | Poul Møller Hansen <freebsd(at)pbnet(dot)dk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Date index not used when selecting a date range |
Date: | 2008-07-28 13:09:40 |
Message-ID: | 488DC514.6030804@pbnet.dk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alex Gen | 2008-07-28 14:37:12 | Connecting to an existing transaction state. |
Previous Message | Abhirama Mallela | 2008-07-28 11:32:02 | Setting up the postgres codebase in Eclipse |