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! ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html