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
SELECT in the father table
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.