Question about indexes on tables with inherited children

Started by Bryan Montgomeryalmost 16 years ago2 messagesgeneral
Jump to latest
#1Bryan Montgomery
monty@english.net

Hello,
A quick question (I think).
If I have Table_A with id integer, val text - and no data.
Table_B inherits from Table_A and has a bunch of data.
Table_C inherits from Table_A and has some data.
Table_X has id integer, someValue integer

If I do select * from Table_A a, Table_X x where a.id = x.id
and it only returns 1 or a few rows, should it use an index if I have an
index built on Table_A using id?
Or do I need an index individually on B & C?
I thought having the index on A would be used, but in an analyze it was
scanning the table until I created indexes on B & C.

If it should have worked, the only thing that I can think of was that the
stats on the tables were maybe out of date? This is on 8.4.3.

Thanks,
Bryan.

#2Sergey Konoplev
gray.ru@gmail.com
In reply to: Bryan Montgomery (#1)
Re: Question about indexes on tables with inherited children

On 16 June 2010 19:07, Bryan Montgomery <monty@english.net> wrote:

If I do select * from Table_A a, Table_X x where a.id = x.id
and it only returns 1 or a few rows, should it use an index if I have an
index built on Table_A using id?

It should because it scans Table_A too.

Or do I need an index individually on B & C?

Yes. Indexes are not inherited.

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802