On Tue, Jul 29, 2008 at 1:25 AM, Poul Møller Hansen <[EMAIL PROTECTED]> 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" <=
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/4
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 t
You can prevent postgres from using the index on node by changing the reference
in the WHERE clause to an expression, like so:
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
On 9:09 am 07/28/08 Poul Møller Hansen <[EMAIL PROTECTED]> 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
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
