Re: Slow SQL lookup due to every field being listed in SORT KEY
| От | Mason Harding |
|---|---|
| Тема | Re: Slow SQL lookup due to every field being listed in SORT KEY |
| Дата | |
| Msg-id | AANLkTikweUAnRDw_kTfFSc26-cTDtCA5WbFJBGq82Xr8@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Slow SQL lookup due to every field being listed in SORT KEY (Stephen Frost <sfrost@snowman.net>) |
| Список | pgsql-performance |
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
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:
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-----
В списке pgsql-performance по дате отправления: