Re: Join query on 1M row table slow

From: CSN <cool_screen_name90001(at)yahoo(dot)com>
To: pgsql General List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Join query on 1M row table slow
Date: 2004-02-10 21:39:09
Message-ID: 20040210213909.91805.qmail@web40603.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Here's the EXPLAIN:

Limit (cost=9595.99..9596.05 rows=25 width=290)
-> Sort (cost=9595.99..9598.45 rows=986
width=290)
Sort Key: p.title
-> Nested Loop (cost=0.00..9546.96 rows=986
width=290)
-> Index Scan using idx_pc_category_id
on product_categories pc (cost=0.00..3607.28 rows=986
width=4)
Index Cond: (category_id = 1016)
-> Index Scan using pkey_products_id
on products p (cost=0.00..6.01 rows=1 width=290)
Index Cond: ("outer".product_id =
p.id)
(8 rows)

Is the "cost" high?

CSN

--- lists(at)natserv(dot)com wrote:
> 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?
> >
> > Redhat 9, PG 7.4.1.
>
> Could you give more info on the hardware?
> You did not mention how often you do your vacuum
> analyze or how often data
> is updated/deleted. The more info you provide the
> more we can try to
> help.
>
> How about your buffer and other settings?

__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

In response to

Browse pgsql-general by date

  From Date Subject
Next Message scott.marlowe 2004-02-10 21:42:23 Re: Join query on 1M row table slow
Previous Message CSN 2004-02-10 21:33:19 Re: Join query on 1M row table slow