Skip site navigation (1) Skip section navigation (2)

Re: Date index not used when selecting a date range

From: "Francisco Reyes" <lists(at)stringsutils(dot)com>
To: Poul Møller Hansen <freebsd(at)pbnet(dot)dk>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Date index not used when selecting a date range
Date: 2008-07-28 17:22:06
Message-ID: 64780831c7d2a30f033a5aa0ffca9c4a@stringsutils.com (view raw or flat)
Thread:
Lists: pgsql-general
On 9:09 am 07/28/08 Poul Møller Hansen  <freebsd(at)pbnet(dot)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?


In response to

Responses

pgsql-general by date

Next:From: Garg, ManjitDate: 2008-07-28 17:32:24
Subject: Re: Clone a database to other machine
Previous:From: Francisco ReyesDate: 2008-07-28 17:11:38
Subject: Re: Clone a database to other machine

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group