From: | John Lange <lists(at)darkcore(dot)net> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Query plan and Inheritance. Weird behavior |
Date: | 2003-01-22 21:44:42 |
Message-ID: | 1043271882.11373.158.camel@johnlaptop.darkcore.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-01-22 22:03:55 | Re: Slow query on OS X box |
Previous Message | Tomasz Myrta | 2003-01-22 20:58:54 | Re: Slow query on OS X box |