From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | Dror Matalon <dror(at)zapatec(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Speeding up Aggregates |
Date: | 2003-10-03 21:07:10 |
Message-ID: | 200310031407.10343.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Dror,
> select articlenumber, channel, description, title, link, dtstamp from
> items, my_channels where items.channel = '22222' and my_channels.id =
> '22222' and owner = 'drormata' and dtstamp > last_viewed and
> articlenumber not in (select item from viewed_items where channel
> ='22222' and owner = 'drormata');
the NOT IN is a bad idea unless the subselect never returns more than a
handful of rows. If viewed_items can grow to dozens of rows, wyou should
use WHERE NOT EXISTS instead. Unless you're using 7.4.
> item_max_date() looks like this:
> select max(dtstamp) from items where channel = $1 and link = $2;
Change it to
SELECT dtstamp from iterm where channel = $1 and link = $2
ORDER BY dtstamp DESC LIMIT 1
and possibly build an index on channel, link, dtstamp
--
-Josh Berkus
Aglio Database Solutions
San Francisco
From | Date | Subject | |
---|---|---|---|
Next Message | Dror Matalon | 2003-10-03 21:28:48 | Re: Speeding up Aggregates |
Previous Message | Tom Lane | 2003-10-03 20:57:56 | Re: reindex/vacuum locking/performance? |