Thanks all for your help. I didn't really understand why it was sorting on every field, but it now makes sense. What I ended up doing was replacing the
SELECT DISTINCT * FROM .... JOIN ... WHERE ... ORDER BY... LIMIT ...
with
SELECT * FROM ... WHERE id in (SELECT DISTINCT id FROM .... JOIN ... WHERE ... ) ORDER BY... LIMIT ...
This reduced the lookup time down to 19 ms, which is much faster than just upping the work_mem, as that still took 800ms
Thanks all,
Mason
On Fri, Sep 10, 2010 at 7:03 PM, Stephen Frost
<sfrost@snowman.net> wrote:
* Tom Lane (
tgl@sss.pgh.pa.us) wrote:
> The reason it's sorting by all the columns is the DISTINCT
if it's included today.. Often developers put that in without
understanding why they're getting dups (which can often be due to
missing pieces from the JOIN clause or misunderstanding of the database
schema...).
Stephen
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
iEYEARECAAYFAkyK43kACgkQrzgMPqB3kihX4ACfVboO4jRzFO3hkckdHfrSeAgF
sysAnjmeoV7BA7uClEY8gXT4nEYhSx0u
=y556
-----END PGP SIGNATURE-----