Received: from mblox.com (unknown [213.155.153.59]) by postgresql.org (Postfix) with ESMTP id D0868476661 for ; Mon, 8 Apr 2002 11:17:47 -0400 (EDT) Received: from salamander (salamander.mblox.com [::ffff:192.168.2.102]) (AUTH: LOGIN ian.cass) by aerosmith with esmtp; Mon, 08 Apr 2002 15:18:16 +0000 Message-ID: <00e001c1df10$5c34a7b0$6602a8c0@salamander> From: "Ian Cass" To: pgsql-sql@postgresql.org Cc: "Tom Lane" References: <002001c1def9$e0fcc750$6602a8c0@salamander> <20623.1018277168@sss.pgh.pa.us> Subject: Re: JOINS and non use of indexes Date: Mon, 8 Apr 2002 16:16:29 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200204/89 X-Sequence-Number: 7354 > There's your problem (and don't tell me Oracle gets this right; they > don't do inheritance, do they?). The planner isn't smart about > indexscan-based joins for inheritance trees. This could possibly be > improved with some work, but I haven't thought about the details. It's true. When I use the monthly tables explicitly and not it's parent, it uses the index properly. Unfortunately this isn't the solution I was looking for :/ select * from messages_200203 as messages, statusinds_200203 as statusinds WHERE (messages.client_id = '7' AND messages.user_name in ('U66515')) AND statusinds.gateway_id = messages.gateway_id limit 5; Limit (cost=0.00..20.69 rows=5 width=455) -> Nested Loop (cost=0.00..13495.79 rows=3261 width=455) -> Index Scan using messages_200203_ix2 on messages_200203 messages (cost=0.00..272.61 rows=68 width=383) -> Index Scan using statusinds_200203_ix1 on statusinds_200203 statusinds (cost=0.00..194.87 rows=48 width=72) -- Ian Cass