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

Re: PG7.4.5: query not using index on date column

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Dave Steinberg <dave-dated-1101824919(dot)46cd20(at)redterror(dot)net>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: PG7.4.5: query not using index on date column
Date: 2004-11-25 18:44:51
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
Dave Steinberg <dave-dated-1101824919(dot)46cd20(at)redterror(dot)net> writes:
>                ->  Seq Scan on messages  (cost=0.00..21573.04 rows=436426 width=54) (actual time=5.523..6304.657 rows=462931 loops=1)
>                      Filter: ((received_date >= '2004-11-01'::date) AND (received_date <= '2004-11-30'::date))

How many rows in the table altogether?  A rough guess is a few million
based on the estimated seqscan cost.  That would mean that this query
is retrieving about 10% of the table, which is a large enough fraction
that the planner will probably think a seqscan is best.  It may be right.
If you do "set enable_seqscan = off", how does the EXPLAIN ANALYZE
output change?

If it's not right, you may want to try to adjust random_page_cost and/or
effective_cache_size so that the planner's estimated costs are more in
line with reality.  Beware of making such adjustments on the basis of
only one test case, though.

			regards, tom lane

In response to


pgsql-sql by date

Next:From: Andrew ThorleyDate: 2004-11-25 21:47:20
Subject: Type Inheritance
Previous:From: Andrew MDate: 2004-11-25 18:43:29
Subject: HowTo change encoding type....

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