From: Robert Creager <Robert_Creager@LogicalChaos.org>
To: Michael Fuhr <mike@fuhr.org>
Cc: PGPerformance <pgsql-performance@postgresql.org>
Subject: Re: Index isn't used during a join.
Date: Tue, 10 Jan 2006 08:17:05 -0700
Message-ID: <20060110081705.221792f9@thunder.logicalchaos.org> (raw)
In-Reply-To: <20060110055818.GA55076@winnie.fuhr.org>
References: <20060109212338.6420af12@thunder.logicalchaos.org>
<20060110055818.GA55076@winnie.fuhr.org>
When grilled further on (Mon, 9 Jan 2006 22:58:18 -0700),
Michael Fuhr <mike@fuhr.org> confessed:
> 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.
Thanks for pointing out the obvious that I missed. Too much data in the second query. It's supposed to match (row wise) what was returned from the first query.
Just ignore me for now...
Thanks,
Rob
--
08:15:24 up 3 days, 42 min, 9 users, load average: 2.07, 2.20, 2.25
Linux 2.6.12-12-2 #4 SMP Tue Jan 3 19:56:19 MST 2006
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: Robert_Creager@LogicalChaos.org, mike@fuhr.org
Subject: Re: Index isn't used during a join.
In-Reply-To: <20060110081705.221792f9@thunder.logicalchaos.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