From: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: Simple query: how to optimize
Date: Sat, 29 Oct 2005 02:12:24 +0200
Message-ID: <20051029001224.GA6611@uio.no> (raw)
In-Reply-To: <DB28E9B548192448A4E8C8A3C1B1E475611DAF@sj1-exch-01.us.corp.kailea.com>
References: <DB28E9B548192448A4E8C8A3C1B1E475611DAF@sj1-exch-01.us.corp.kailea.com>
On Fri, Oct 28, 2005 at 03:40:40PM -0700, Roger Hand wrote:
> You're first joining against the entire user table, then filtering out the users
> you don't need.
That's just wrong, sorry -- the planner is perfectly able to push the WHERE
down before the join.
I'd guess the problem is the age() query; age() doesn't really return what
you'd expect, and I don't think it can use an index easily (I might be wrong
here, though). Instead, try something like
WHERE u.joined_date >= current_date - interval '30 days'
except that if you're running pre-8.0, you might want to precalculate the
right-hand side on the client.
I couldn't see EXPLAIN ANALYZE of your query, BTW -- having it would be
useful.
/* Steinar */
--
Homepage: http://www.sesse.net/
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: sgunderson@bigfoot.com
Subject: Re: Simple query: how to optimize
In-Reply-To: <20051029001224.GA6611@uio.no>
* 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