From: Michael Fuhr <mike@fuhr.org>
To: Robert Creager <Robert_Creager@LogicalChaos.org>
Cc: PGPerformance <pgsql-performance@postgresql.org>
Subject: Re: Index isn't used during a join.
Date: Mon, 9 Jan 2006 22:58:18 -0700
Message-ID: <20060110055818.GA55076@winnie.fuhr.org> (raw)
In-Reply-To: <20060109212338.6420af12@thunder.logicalchaos.org>
References: <20060109212338.6420af12@thunder.logicalchaos.org>
On Mon, Jan 09, 2006 at 09:23:38PM -0700, Robert Creager wrote:
> I'm working with a query to get more info out with a join. The base
> query works great speed wise because of index usage. When the join is
> tossed in, the index is no longer used, so the query performance tanks.
The first query you posted returns 285 rows and the second returns
over one million; index usage aside, that difference surely accounts
for a performance penalty. And as is often pointed out, index scans
aren't always faster than sequential scans: the more of a table a
query has to fetch, the more likely a sequential scan will be faster.
Have the tables been vacuumed and analyzed? The planner's estimates
for windspeed are pretty far off, which could be affecting the query
plan:
> -> Sort (cost=12997.68..13157.98 rows=64120 width=28) (actual time=2286.155..2286.450 rows=284 loops=1)
> Sort Key: date_part('doy'::text, unmunge_time(windspeed.time_group))
> -> Seq Scan on windspeed (cost=0.00..7878.18 rows=64120 width=28) (actual time=2279.275..2285.271 rows=284 loops=1)
> Filter: (unmunge_time(time_group) > (now() - '24:00:00'::interval))
That's a small amount of the total query time, however, so although
an index scan might help it probably won't provide the big gain
you're looking for.
Have you done any tests with enable_seqscan disabled? That'll show
whether an index or bitmap scan would be faster. And have you
verified that the join condition is correct? Should the query be
returning over a million rows?
--
Michael Fuhr
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mike@fuhr.org, Robert_Creager@LogicalChaos.org
Subject: Re: Index isn't used during a join.
In-Reply-To: <20060110055818.GA55076@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox