From: Collin Peters <cadiolis@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Simple query: how to optimize
Date: Fri, 28 Oct 2005 17:04:32 -0700
Message-ID: <df01c91b0510281704u552a6b2ree60eec6b371ef74@mail.gmail.com> (raw)
In-Reply-To: <df01c91b0510281656gcae9b79r31fd096f0b31eefa@mail.gmail.com>
References: <DB28E9B548192448A4E8C8A3C1B1E475611DAF@sj1-exch-01.us.corp.kailea.com>
<df01c91b0510281656gcae9b79r31fd096f0b31eefa@mail.gmail.com>
A little bit more on my last post that I forget to mention. The two
queries run at the same speed and have the same plan only if I have an
index on the user_activity.user_id column. Otherwise they run at
different speeds. The query you gave me actually runs slower without
the index. All this is making my head spin!! :O
On 10/28/05, Collin Peters <cadiolis@gmail.com> wrote:
> 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: <df01c91b0510281704u552a6b2ree60eec6b371ef74@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