Re: Slow SQL lookup due to every field being listed in SORT KEY

[Mason Harding <mason.harding@xxxxxxxxx>]

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@xxxxxxxxxxx> wrote:

* Tom Lane (tgl@xxxxxxxxxxxxx) 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-----