Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 892F8475F59 for ; Mon, 8 Apr 2002 10:45:49 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g38Ek8F20626; Mon, 8 Apr 2002 10:46:08 -0400 (EDT) To: "Ian Cass" Cc: pgsql-sql@postgresql.org Subject: Re: JOINS and non use of indexes In-reply-to: <002001c1def9$e0fcc750$6602a8c0@salamander> References: <002001c1def9$e0fcc750$6602a8c0@salamander> Comments: In-reply-to "Ian Cass" message dated "Mon, 08 Apr 2002 13:35:28 +0100" Date: Mon, 08 Apr 2002 10:46:08 -0400 Message-ID: <20623.1018277168@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200204/87 X-Sequence-Number: 7352 "Ian Cass" writes: > I'm transferring a database & reporting scripts across from Oracle & a few > of the SQL statements behave a little differently with regards to indexes. > The one that's got me stumped at the moment is this... > select * from messages, statusinds > WHERE statusinds.gateway_id = messages.gateway_id > AND (messages.client_id = '7' AND messages.user_name in ('U66515')) > limit 5; > (tables_YYYYMM are inherited) 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. regards, tom lane