1

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 it seems that PostgreSQL uses each child's index, instead of use only the father index.

Is it possible to improve this behavior?

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

SELECT in the child table

enter image description here

SELECT in the father table

enter image description here

8
  • 1
    Lead with your version of Postgres in any such question please. It's particularly relevant with your issue. Commented May 26, 2020 at 1:27
  • @ErwinBrandstetter, Yes, sir! Done! You help me with this question some years ago! The time to improve performance arrived! As you predicted! =D Commented May 26, 2020 at 1:33
  • 1
    PostgreSQL 9.2 has reached EOL in 2017! Is upgrading to a current version possible? Modern day declarative partitioning >>> old school inheritance. For starters, there is no "father INDEX" in your model. Indexes apply to each table only. Commented May 26, 2020 at 1:53
  • 2
    Please don't post execution plans as screen shots, formatted text is much easier to read. And whenever possible post the output of explain (analyze, buffers, format text) - not just a "simple" explain. To preserve the indention of the plan, paste the plan's text, then put ``` on the line before the plan and on a line after the plan.
    – user1822
    Commented May 26, 2020 at 5:47
  • 1
    Do you want data from all "children" tables when you select from the "father" table? Also, if the data center doesn't support anything less ancient than 9.2, it is time to change data center. Commented May 26, 2020 at 9:01

1 Answer 1

1

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.

To search in the father table only, use the ONLY keyword:

 SELECT FROM ONLY MasterAccount.imovel ....

The manual:

If ONLY is specified before the table name, only that table is scanned.

1
  • When i use ONLY in the parent table, no rows are returned. I think it is because inserts are done in child tables. Commented May 26, 2020 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.