Improve PostgreSQL SELECT performance in a parent table (inheritance)

Question

I have a PostgreSQL 9.2 database where each account has a schema, like below:

My_Database
 |-> Schemas
    |-> AccountA
    |-> AccountB
    |-> AccountC
    |-> AccountD
    |-> AccountE
           .
           .
           .
    |-> AccountZ
    |-> MasterAccount

All schemas have a table called imovel which has a column called id, the structure is:

CREATE SEQUENCE MasterAccount.imovel_id_seq;

CREATE TABLE MasterAccount.imovel (
    id integer DEFAULT nextval('MasterAccount.imovel_id_seq') NOT NULL PRIMARY KEY,
    title varchar(80) NOT NULL
);

CREATE TABLE AccountA.imovel (
    id integer DEFAULT nextval('MasterAccount.imovel_id_seq') PRIMARY KEY
)
INHERITS (MasterAccount.imovel);

.
.
.

CREATE TABLE AccountZ.imovel (
    id integer DEFAULT nextval('MasterAccount.imovel_id_seq') PRIMARY KEY
)
INHERITS (MasterAccount.imovel);

Today i have 127 accounts and when i run a SELECT in a child table, the response is fast. But when i run the same SELECT in the father table, it is slow. I saw the EXPLAIN and seems that PostgreSQL uses each child's INDEX, instead of use only the father INDEX.

Is it possible to improve this behavior? Below are the EXPLAIN.

I need to use SELECT in the Master table for global searches.

SELECT in the child table

SELECT in the father table

Database query-performance questions are better asked on dba.stackexchange.com — Andreas
– Andreas, Commented May 26, 2020 at 1:15
I would have expected a query like that to process in parallel. Since it doesn't, according to the access plan, you might want to look into the parallel settings of your database, to make sure parallel processing is not disabled. — Andreas
– Andreas, Commented May 26, 2020 at 1:24
@Andreas, Thanks, i will check! Do you know what is the directive config, please? — Marcio Mazzucato
– Marcio Mazzucato, Commented May 26, 2020 at 1:36

Laurenz Albe · Accepted Answer · 2020-05-26 08:12:51Z

3

Selecting from MasterAccount.imovel will select from all child tables, so it is not surprising that it takes much longer. The query won't use the index on MasterAccount.imovel because that table itself contains no data, so it makes no sense to use the index. I guess your misconception is that an index on the parent table will index all child tables, but that is not the case.

You also cannot benefit from parallel query because you are using a very old, outdated PostgreSQL version. Upgrading to v12 would help, but only concerning the query response time: parallel processing does not reduce the resources used.

answered May 26, 2020 at 8:12

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Improve PostgreSQL SELECT performance in a parent table (inheritance)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related