Hello,
We have partitioned tables in two levels. Both stages are partitioned in
ranges method. We see that planner and executor time was 10 time slower when
we asked main table rather than partitioned. My question is did planner and
executor are working optimal? I have doubts about it. Let's consider that
situation, because I think that in some ways (I think in more cases) planner
shoudl be more optimal. When we have table partitioned by key which is in
"where" clause we have guarantee that all rows we can find just in ONE
partition: contained in range or in default if exists. Planner show that
query should be executed just in one partition, but it takes a lot of time.
So my direct question is if planner is stopping searching for another
partition when he found one correct? Are partition ranges stored sorted and
searching method is optimal and is stopped after first (and only) hit?
I've noticed that increasing numbers of partition proportionally increase
planner time. Additionally having index on column that you are searching for
is adding extra time (one index add +/- 100% time for table). It's
understandable but optimizing planner and executor by ideas I wrote on first
paragraph automatically decrease time for searching indexes.
Reproduction:
1. ADD MAIN TABLE
-- Table: public.book
--DROP TABLE public.book;
CREATE TABLE public.book
(
id bigserial,
id_owner bigint NOT NULL,
added date NOT NULL
) PARTITION BY RANGE (id_owner)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.book
OWNER to postgres;
2. ADD PARTITIONS (run first "a" variant, then drop table book, reconstruct
and run "variant"):
a. 1200 partitions:
https://gist.github.com/piotrwlodarczyk/4faa05729d1bdd3b5f5738a2a3faabc0
b. 6000 partitions:
https://gist.github.com/piotrwlodarczyk/2747e0984f521768f5d36ab2b382ea36
3. ANALYZE ON MAIN TABLE:
EXPLAIN ANALYZE SELECT * FROM public.book WHERE id_owner = 4;
a. My result for 1200 partitions:
https://gist.github.com/piotrwlodarczyk/500f20a0b6e2cac6d36ab88d4fea2c00
b. My result for 6000 partitions:
https://gist.github.com/piotrwlodarczyk/277687b21201340377116a18a3dd8be8
4. ANALYZE ON PARTITIONED TABLE (only on first level):
EXPLAIN ANALYZE SELECT * FROM public.book WHERE id_owner = 4;
a. My result for 1200:
https://gist.github.com/piotrwlodarczyk/4285907c68b34b486cbf39eb8ae5cf92
b. My result for 6000:
https://gist.github.com/piotrwlodarczyk/c157cc9321b6e1a1d0f900310f14f1cc
4. CONCLUSIONS
Planner time for select on public.book (main table) 1200 was 469.416 ms,
for 6000 was 2530.179 ms. It looks like time is linear to partition count.
That makes me sure that all partitions are checked instead of searching for
first that equals. Intentionally I've searching id_owner = 4 to make sure
that in both cases first partition should by marked as correct and planer
time should be constant. What is intereting too that real execution time was
+/- equal in both cases. Is executor working better than planner?
When we're asking on first level partition directly - time for planner
1200 is 58.736 ms, for 6000: 60.555 ms. We can say it's equal. Why? Because
planner don't have to search for another matching partitions because first
found can match. It's guaranteed by rule that say ranges in partitions
cannot override. Execution time in this case is 50 times faster!
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
