Join query on 1M row table slow

[CSN]

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_categor ies 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.

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[lists]

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_categor ies 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?

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Richard Huxton]

On Tuesday 10 February 2004 19:51, 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_categor ies 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?

Quite possibly - I'd suggest:
1. Read "Performanc e Tuning" and the "Annotated conf file" at
http://www.varlena.com/varlena/Gener...bits/index.php
2. Vacuum analyze the tables concerned and post the output of EXPLAIN ANALYSE
with your query.
3. Next time, try the -performance list - probably get a faster response
there.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[CSN]

> 2. Vacuum analyze the tables concerned and post the

output of EXPLAIN ANALYSE
with your query.

=> explain analyze;

results in:

ERROR: syntax error at or near ";" at character 16

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[scott.marlowe]

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_categor ies 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_categor ies pc where pc.category_id = $category_id)
order by p.title limit 25 offset $offset

? Or is that equivalent?
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[CSN]

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..954 6.96 rows=986
width=290)
-> Index Scan using idx_pc_category _id
on product_categor ies pc (cost=0.00..360 7.28 rows=986
width=4)
Index Cond: (category_id = 1016)
-> Index Scan using pkey_products_i d
on products p (cost=0.00..6.0 1 rows=1 width=290)
Index Cond: ("outer".produc t_id =
p.id)
(8 rows)
Is the "cost" high?

CSN
--- li***@natserv.c om 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_categor ies 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[scott.marlowe]

On Tue, 10 Feb 2004, CSN wrote:

2. Vacuum analyze the tables concerned and post the
output of EXPLAIN ANALYSE
with your query.

=> explain analyze;

results in:

ERROR: syntax error at or near ";" at character 16

No silly. you do:

explain analyze select ... (rest of the query...)

and it runs the query and tells you how long each bit took and what it
THOUGHT it would get back in terms of number of rows and what it actually
got back.

Let us know...
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[CSN]

--- "scott.marl owe" <sc***********@ ihs.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_categor ies 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_categor ies pc where
pc.category_id = $category_id)
order by p.title limit 25 offset $offset

? Or is that equivalent?

I think that probably improves things (lower cost? -
see my other post):

explain select p.* from products p where p.id in (
select product_id from product_categor ies pc where
pc.category_id = 1016) order by p.title limit 25
offset 0;

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Limit (cost=4282.18.. 4282.24 rows=25 width=290)
-> Sort (cost=4282.18.. 4282.46 rows=111
width=290)
Sort Key: p.title
-> Nested Loop (cost=3609.75.. 4278.41
rows=111 width=290)
-> HashAggregate
(cost=3609.75.. 3609.75 rows=111 width=4)
-> Index Scan using
idx_pc_category _id on product_categor ies pc
(cost=0.00..360 7.28 rows=986 width=4)
Index Cond: (category_id =
1016)
-> Index Scan using pkey_products_i d
on products p (cost=0.00..6.0 1 rows=1 width=290)
Index Cond: (p.id =
"outer".product _id)
(9 rows)
I figured the limit/offset was probably causing the
problem. What's weird is that when the same query is
executed again, it seems much faster - some sort of
caching maybe?

(table pc is just product_id <=> category_id - I don't
really need the category_id)

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[scott.marlowe]

On Tue, 10 Feb 2004, CSN wrote:

I think that probably improves things (lower cost? -
see my other post):

explain select p.* from products p where p.id in (
select product_id from product_categor ies pc where
pc.category_id = 1016) order by p.title limit 25
offset 0;

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Limit (cost=4282.18.. 4282.24 rows=25 width=290)
-> Sort (cost=4282.18.. 4282.46 rows=111
width=290)
Sort Key: p.title
-> Nested Loop (cost=3609.75.. 4278.41
rows=111 width=290)
-> HashAggregate
(cost=3609.75.. 3609.75 rows=111 width=4)
-> Index Scan using
idx_pc_category _id on product_categor ies pc
(cost=0.00..360 7.28 rows=986 width=4)
Index Cond: (category_id =
1016)
-> Index Scan using pkey_products_i d
on products p (cost=0.00..6.0 1 rows=1 width=290)
Index Cond: (p.id =
"outer".product _id)
(9 rows)
I figured the limit/offset was probably causing the
problem. What's weird is that when the same query is
executed again, it seems much faster - some sort of
caching maybe?>
Yep. Exactly.

What does explain analyze say about the two queries?
(table pc is just product_id <=> category_id - I don't
really need the category_id)

If you could eliminate the need for that table in this query you should
get it to run much faster.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[CSN]

Doh! Yeah, now I remember ;)

QUERY 1:

=> explain analyze select p.* from product_categor ies
pc inner join products p on pc.product_id = p.id where
pc.category_id = 1016 order by p.title limit 25 offset
0;

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=9595.99.. 9596.05 rows=25 width=290)
(actual time=514.808..5 14.942 rows=25 loops=1)
-> Sort (cost=9595.99.. 9598.45 rows=986
width=290) (actual time=514.794..5 14.868 rows=25
loops=1)
Sort Key: p.title
-> Nested Loop (cost=0.00..954 6.96 rows=986
width=290) (actual time=0.672..421 .732 rows=2358
loops=1)
-> Index Scan using idx_pc_category _id
on product_categor ies pc (cost=0.00..360 7.28 rows=986
width=4) (actual time=0.343..125 .762 rows=2358
loops=1)
Index Cond: (category_id = 1016)
-> Index Scan using pkey_products_i d
on products p (cost=0.00..6.0 1 rows=1 width=290)
(actual time=0.075..0.0 83 rows=1 loops=2358)
Index Cond: ("outer".produc t_id =
p.id)
Total runtime: 516.174 ms
(9 rows)
QUERY 2:

=> explain analyze select p.* from products p where
p.id in ( select product_id from product_categor ies pc
where pc.category_id = 1016) order by p.title limit 25
offset 0;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4282.18.. 4282.24 rows=25 width=290)
(actual time=447.852..4 47.979 rows=25 loops=1)
-> Sort (cost=4282.18.. 4282.46 rows=111
width=290) (actual time=447.836..4 47.904 rows=25
loops=1)
Sort Key: p.title
-> Nested Loop (cost=3609.75.. 4278.41
rows=111 width=290) (actual time=104.256..3 58.182
rows=2358 loops=1)
-> HashAggregate
(cost=3609.75.. 3609.75 rows=111 width=4) (actual
time=103.922..1 14.836 rows=2358 loops=1)
-> Index Scan using
idx_pc_category _id on product_categor ies pc
(cost=0.00..360 7.28 rows=986 width=4) (actual
time=0.401..92. 253 rows=2358 loops=1)
Index Cond: (category_id =
1016)
-> Index Scan using pkey_products_i d
on products p (cost=0.00..6.0 1 rows=1 width=290)
(actual time=0.069..0.0 76 rows=1 loops=2358)
Index Cond: (p.id =
"outer".product _id)
Total runtime: 449.370 ms
(10 rows)
-CSN
--- "scott.marl owe" <sc***********@ ihs.com> wrote:

On Tue, 10 Feb 2004, CSN wrote:

2. Vacuum analyze the tables concerned and post the output of EXPLAIN ANALYSE
with your query.

=> explain analyze;

results in:

ERROR: syntax error at or near ";" at character

16

No silly. you do:

explain analyze select ... (rest of the query...)

and it runs the query and tells you how long each
bit took and what it
THOUGHT it would get back in terms of number of rows
and what it actually
got back.

Let us know...

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly