From: Collin Peters <cadiolis@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Simple query: how to optimize
Date: Fri, 28 Oct 2005 16:56:40 -0700
Message-ID: <df01c91b0510281656gcae9b79r31fd096f0b31eefa@mail.gmail.com> (raw)
In-Reply-To: <DB28E9B548192448A4E8C8A3C1B1E475611DAF@sj1-exch-01.us.corp.kailea.com>
References: <DB28E9B548192448A4E8C8A3C1B1E475611DAF@sj1-exch-01.us.corp.kailea.com>
These two queries execute at exactly the same speed. When I run run
EXPLAIN on them both they return the *exact* same query plan as well.
I find this strange... but it is also kind of what I expected from
reading up on various things. I am under the impression the
postgresql will break up your query and run it as it sees best. So
in the case of these two queries... it seems it is actually almost
converting one into the other. Maybe I am wrong.
Is there a good resource list somewhere for postgresql query
optimization? There are entire books devoted to the subject for
oracle but I can't find more than a few small articles on postgresql
query optimizations on the web.
Regards,
Collin
On 10/28/05, Roger Hand <RHand@kailea.com> wrote:
> > SELECT u.user_id, MAX(ua.activity_date)
> > FROM pp_users u
> > LEFT OUTER JOIN user_activity ua ON (u.user_id = ua.user_id AND
> > ua.user_activity_type_id = 7)
> > WHERE u.userstatus_id <> 4
> > AND age(u.joined_date) < interval '30 days'
> > GROUP BY u.user_id
>
> You're first joining against the entire user table, then filtering out the users
> you don't need.
>
> Instead, filter out the users you don't need first, then do the join:
>
> SELECT users.user_id, MAX(ua.activity_date)
> FROM
> (SELECT u.user_id
> FROM pp_users u
> WHERE u.userstatus_id <> 4
> AND age(u.joined_date) < interval '30 days'
> ) users
> LEFT OUTER JOIN user_activity ua
> ON (users.user_id = ua.user_id
> AND ua.user_activity_type_id = 7)
> GROUP BY users.user_id
>
> (disclaimer: I haven't actually tried this sql)
>
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: cadiolis@gmail.com
Subject: Re: Simple query: how to optimize
In-Reply-To: <df01c91b0510281656gcae9b79r31fd096f0b31eefa@mail.gmail.com>
* 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