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 <sfr...@snowman.net> wrote: > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > The reason it's sorting by all the columns is the DISTINCT > > You might also verify that you actually need/*should* have 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----- > >
