Skip site navigation (1) Skip section navigation (2)

Re: Tuning questions..

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Michael T(dot) Halligan" <michael(at)echo(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Tuning questions..
Date: 2001-12-19 23:04:31
Message-ID: 4112.1008803071@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
"Michael T. Halligan" <michael(at)echo(dot)com> writes:
> The query sorts through about 80k rows.. here's the query
> --------------------------------------------------
> SELECT count(*) FROM (
>                   SELECT DISTINCT song_id FROM ssa_candidate WHERE 
> style_id IN (
>                              SELECT style_id FROM station_subgenre WHERE 
> station_id = 48
>                                             )
>                             ) AS X;

The standard advice for speeding up WHERE ... IN queries is to convert
them to WHERE ... EXISTS.  However, assuming that there are not very
many style_ids for any one station_id in station_subgenre, this probably
won't help much.  What I'd try is converting it to a straight join:

SELECT count(DISTINCT song_id) FROM ssa_candidate, station_subgenre
WHERE 
    ssa_candidate.style_id = station_subgenre.style_id AND
    station_id = 48;

Normally this would not do what you want, since you could end up with
multiple joined rows for any one ssa_candidate row, but given that
you're going to do a DISTINCT that doesn't really matter.  Better to
let the thing use a more efficient join method and just throw away the
extra rows in the DISTINCT step.  Or that's my theory anyway; let us
know how well it works.

BTW, are the row estimates in the EXPLAIN output anywhere close to
reality?

			regards, tom lane

In response to

pgsql-admin by date

Next:From: Michael T. HalliganDate: 2001-12-19 23:52:47
Subject: Re: [Tuning questions..]
Previous:From: banghDate: 2001-12-19 22:21:06
Subject: Re: poor performance of loading data

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group