tag, minor edits

Source Link

edited May 26, 2020 at 3:11

182.1k
28
457
620

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 iI have 127 accounts and when iI run a SELECT in a child table, the response is fast. But when iI run the same SELECT in the father table, it is slow. I saw the EXPLAIN and it seems that PostgreSQL uses each child's INDEXindex, instead of use only the father INDEXindex.

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

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

SELECT in the child table

SELECT in the father table

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

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

edited title

Link

edited May 26, 2020 at 2:40

Marcio Mazzucato

113
5

Improve PostgreSQL SELECT performance in a fatherparent table (inheritance)

added 4 characters in body

Source Link

edited May 26, 2020 at 1:29

Marcio Mazzucato

113
5

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

I have a PostgreSQL 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

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

Source Link

asked May 26, 2020 at 1:19

Marcio Mazzucato

113
5

Loading

Stack Exchange Network

Return to Question

Improve PostgreSQL SELECT performance in a fatherparent table (inheritance)