pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: John Lange <lists@darkcore.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query plan and Inheritance. Weird behavior
Date: Wed, 22 Jan 2003 16:59:13 -0800 (PST)
Message-ID: <20030122165418.Q4204-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <1043271882.11373.158.camel@johnlaptop.darkcore.net>


On 22 Jan 2003, John Lange wrote:

> 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?

AFAIK no since indices aren't inherited.

> 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).

It seems reasonable to me since given the # of rows and the estimated
row width the table is probably only like 5 or 6 pages.  Reading the index
is unlikely to make life much better given an index read, seek in heap
file, read heap file page.

> I can't say that I'm reading these results properly but here they are:
>
> "EXPLAIN select * from tbl_objects where id = 1;"

This gets any rows in tbl_objects that have id=1 and any rows in any
subtables that have id=1.  Is that the intended effect?





view thread (11+ messages)  latest in thread

Message-ID: <20030122165418.Q4204-100000@megazone23.bigpanda.com>
Permalink:  ../20030122165418.Q4204-100000@megazone23.bigpanda.com/
Also on:    postgresql.org/message-id/20030122165418.Q4204-100000@megazone23.bigpanda.com

 · 

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: sszabo@megazone23.bigpanda.com, lists@darkcore.net
  Subject: Re: Query plan and Inheritance. Weird behavior
  In-Reply-To: <20030122165418.Q4204-100000@megazone23.bigpanda.com>
  References: <1043271882.11373.158.camel@johnlaptop.darkcore.net> <20030122165418.Q4204-100000@megazone23.bigpanda.com>

* 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