why Declarative partitioning on Postgresql 10 is slower than the non partitioned tables?

why Declarative partitioning on Postgresql 10 is slower than the non partitioned tables?

I have partitioned 3 table on a date criteria, but found significantly slower select queries.

Can you figure out why?

this is my partitioned table

CREATE TABLE xxx.documentstate (
    iddoc int8 NOT NULL DEFAULT nextval('xxx.document_state_iddoc_seq'::regclass),
    documento uuid NULL,
    idstate int8 NULL,
    iduser int4 NULL,
    idprocessor int8 NULL,
    datecreate timestamp NULL,
    usercreate varchar NULL,
    datemodifie timestamp NULL,
    usermodifie varchar NULL,
    datestate timestamp NULL,
    iddocuments int8 NULL,
    requestc int8 NULL,
    details varchar NULL
) PARTITION BY RANGE (datecreate)
WITH (
    OIDS = FALSE,
    toast.autovacuum_enabled = TRUE
)
TABLESPACE pg_default;
ALTER TABLE xxx.documentstate
    OWNER to postgres;

CREATE INDEX idx_documentstatedatecreated201601 ON xxx.documentstate201601 USING btree (date_created);

CREATE INDEX idx_documentstate_id_docu201601 ON xxx.documentstate201601 USING btree (iddoc);

CREATE INDEX ind_documentstate_id_documents201601 ON xxx.documentstate201601 USING btree (iddocuments);

CREATE INDEX ind_documentstate_id_state201601 ON xxx.documentstate201601 USING btree (idstate);

CREATE INDEX ind_documentstate_id_state_brin201601 ON xxx.documentstate201601 USING brin (idstate);

explain analyze

SELECT count(d.iddoc) , d.idcompanyservice
FROM xxx.document d
inner join xxx.documentstate ds on ds.iddoc = d.iddoc 
where ds.idstate = 20010
and d.idcompanyservice is not null
group by idcompanyservice

`Finalize GroupAggregate (cost=935420631.26..935420636.26 rows=200 width=16) (actual time=110535.775..110535.784 rows=29 loops=1)
Group Key: d.id_companyservice
-> Sort (cost=935420631.26..935420632.26 rows=400 width=12) (actual time=110535.773..110535.773 rows=62 loops=1)
Sort Key: d.id_companyservice
Sort Method: quicksort Memory: 27kB
-> Gather (cost=935420571.97..935420613.

You didn't match the partitioning criteria in your query. Thus, the database needed to search all partitioned tables to find the results.

You should partition a table by the column that you most frequently use in queries - it will allow the planner to ignore partitions that do not contain rows matching the criteria.

StackExchange Database Administrators Q#193402, answer score: 3