pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John Lange <lists@darkcore.net>
To: pgsql-performance@postgresql.org
Subject: Query plan and Inheritance. Weird behavior
Date: 22 Jan 2003 15:44:42 -0600
Message-ID: <1043271882.11373.158.camel@johnlaptop.darkcore.net> (raw)

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




view thread (11+ messages)  latest in thread

Message-ID: <1043271882.11373.158.camel@johnlaptop.darkcore.net>
Permalink:  ../1043271882.11373.158.camel@johnlaptop.darkcore.net/
Also on:    postgresql.org/message-id/1043271882.11373.158.camel@johnlaptop.darkcore.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: lists@darkcore.net
  Subject: Re: Query plan and Inheritance. Weird behavior
  In-Reply-To: <1043271882.11373.158.camel@johnlaptop.darkcore.net>
  References: <1043271882.11373.158.camel@johnlaptop.darkcore.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox