From: | Cott Lang <cott(at)internetstaff(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Problem with query plan |
Date: | 2004-10-22 19:04:24 |
Message-ID: | 1098471864.3551.31.camel@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have come up with a simple query that runs horribly depending on the
number of columns selected.
select order_lines.*
from orders, order_lines
where orders.merchant_order_id = '11343445' and
order_lines.order_id=orders.order_id;
merchant_order_id is indexed.
order_id is indexed.
Tables are analyzed.
I get the following plan:
---------------------------------------------------------
Merge Join (cost=nan..nan rows=3 width=1257)
Merge Cond: ("outer".order_id = "inner".order_id)
-> Sort (cost=5.33..5.33 rows=2 width=4)
Sort Key: orders.order_id
-> Index Scan using ak_po_number on orders (cost=0.00..5.32
rows=2 width=4)
Index Cond: ((merchant_order_id)::text =
'11343445'::text)
-> Sort (cost=nan..nan rows=2023865 width=1257)
Sort Key: order_lines.order_id
-> Seq Scan on order_lines (cost=0.00..83822.65 rows=2023865
width=1257)
If I restrict the columns (i.e., select 1 from ...), it works great.
I can add columns and it seems that once I get a width of more than
~610, it executes a Merge Join of cost nan that takes forever to return.
If I reduce the columns returned to slightly below this, I get a much
nicer plan:
----------------------------------------------------------
Nested Loop (cost=0.00..16.60 rows=4 width=606)
-> Index Scan using ak_po_number on orders (cost=0.00..5.69 rows=3
width=4)
Index Cond: ((merchant_order_id)::text = '11343445'::text)
-> Index Scan using ak_order_line_doid on order_lines
(cost=0.00..3.61 rows=2 width=610)
Index Cond: (order_lines.order_id = "outer".order_id)
Is this possibly just an overflow that causes a NaN that isn't properly
handled by the optimizer?
This is on Redhat 3.0 AS U3 x86 with the RPMs from postgresql.org.
Thanks!
From | Date | Subject | |
---|---|---|---|
Next Message | Eric E | 2004-10-22 19:18:43 | Re: PlPERL and shared libraries on Suse |
Previous Message | Jan Wieck | 2004-10-22 18:18:52 | Re: Two questions from the boss (SQL:2003 && scalability) |