From: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
---|---|
To: | CSN <cool_screen_name90001(at)yahoo(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Join query on 1M row table slow |
Date: | 2004-02-10 21:22:05 |
Message-ID: | Pine.LNX.4.33.0402101416410.29472-100000@css120.ihs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, 10 Feb 2004, CSN wrote:
> I have a pretty simple select query that joins a table
> (p) with 125K rows with another table (pc) with almost
> one million rows:
>
> select p.*
> from product_categories pc
> inner join products p
> on pc.product_id = p.id
> where pc.category_id = $category_id
> order by p.title
> limit 25
> offset $offset
>
> The query usually takes about five seconds to execute
> (all other PG queries perform fast enough). I have
> indexes on everything needed, and EXPLAIN shows
> they're being used. Is there anything else I can do to
> improve performance - such as tweaking some settings
> in the config?
The problem is that in order to do an offset / limit on such a set,
postgresql is gonna have to generate $offset + limit of the joined set.
So, it's quite likely that it's generating the whole set first.
It also looks odd having a select p.* from product_cat pc, but not
selecting anything from the pc table.
Could this be rewritten to something like
select p.* from products p where p.id in (select
product_id from product_categories pc where pc.category_id = $category_id)
order by p.title limit 25 offset $offset
? Or is that equivalent?
From | Date | Subject | |
---|---|---|---|
Next Message | CSN | 2004-02-10 21:25:26 | Re: Join query on 1M row table slow |
Previous Message | Oleg Lebedev | 2004-02-10 21:17:02 | Re: Upgrade function problem - c language |