Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id EF88D476FAE for ; Wed, 22 Jan 2003 17:27:49 -0500 (EST) Received: from johnlaptop.darkcore.net (link.clearoption.com [205.200.121.81]) by postgresql.org (Postfix) with ESMTP id E262B47711C for ; Wed, 22 Jan 2003 16:44:41 -0500 (EST) Received: by johnlaptop.darkcore.net (Postfix, from userid 501) id 51237F1EB4; Wed, 22 Jan 2003 16:44:42 -0500 (EST) Subject: Query plan and Inheritance. Weird behavior From: John Lange To: pgsql-performance@postgresql.org Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8-3mdk Date: 22 Jan 2003 15:44:42 -0600 Message-Id: <1043271882.11373.158.camel@johnlaptop.darkcore.net> Mime-Version: 1.0 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/231 X-Sequence-Number: 878 I have a database that makes fairly extensive use of table Inheritance. Structure is one parent table and 5 child tables as follows: tbl_objects (parent table) -> tbl_viewers -> tbl_documents -> tbl_icons -> tbl_massemails -> tbl_formats I have two questions: First, if I create an index on the parent table will queries to the child tables use that index? Secondly, I tried to use explain to find out but I got very strange results. It appears to read all the child tables even when you specify only the parent table. In this case this appears to make the select do 6 queries instead of only 1. Obviously a huge performance hit. And none of them uses the index though the table only has 420 rows at the moment so that might be why its just doing a scan (though IMHO 'explain' should explain that it isn't using the available index and why). I can't say that I'm reading these results properly but here they are: "EXPLAIN select * from tbl_objects where id = 1;" Gives: NOTICE: QUERY PLAN: Result (cost=0.00..27.25 rows=6 width=138) -> Append (cost=0.00..27.25 rows=6 width=138) -> Seq Scan on tbl_objects (cost=0.00..12.24 rows=1 width=73) -> Seq Scan on tbl_viewers tbl_objects (cost=0.00..1.07 rows=1 width=83) -> Seq Scan on tbl_documents tbl_objects (cost=0.00..11.56 rows=1 width=78) -> Seq Scan on tbl_massemails tbl_objects (cost=0.00..0.00 rows=1 width=138) -> Seq Scan on tbl_formats tbl_objects (cost=0.00..1.12 rows=1 width=80) -> Seq Scan on tbl_icons tbl_objects (cost=0.00..1.25 rows=1 width=89) Can anyone tell me if these results are making any sense and why postgres is doing 6 reads when I only need one? John Lange