version - postgres 9.6.
I were not so clear in question i asked in past and someone already answer there, so i thought best will be to post new question with more clear info and be more specific about my question.
Trying to join event table with dimension table. event table is a daily partition (3k children) table with check constraints.The event table has 72 columns (i suspect that this is the issue).
I simplify the query in order to demonstrate the question (in practice range is wider and i query field from both tables).
You can see that for this simple query - the plan take almost 10 seconds (my question is about plan time and not execution time). If i query direct on the child table ( please dont advice to use union on all child in range ) query plan is few ms.
explain analyze select campaign_id , spent as spent from events_daily r left join report_campaigns c on r.campaign_id = c.c_id where date >= '20170720' and date < '20170721' ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.29..28.88 rows=2 width=26) (actual time=0.021..0.021 rows=0 loops=1)
-> Append (cost=0.00..12.25 rows=2 width=26) (actual time=0.003..0.003 rows=0 loops=1)
-> Seq Scan on events_daily r (cost=0.00..0.00 rows=1 width=26) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((date >= '2017-07-20 00:00:00'::timestamp without time zone) AND (date < '2017-07-21 00:00:00'::timestamp without time zone))
-> Seq Scan on events_daily_20170720 r_1 (cost=0.00..12.25 rows=1 width=26) (actual time=0.000..0.000 rows=0 loops=1)
Filter: ((date >= '2017-07-20 00:00:00'::timestamp without time zone) AND (date < '2017-07-21 00:00:00'::timestamp without time zone))
-> Index Only Scan using report_campaigns_campaign_idx on report_campaigns c (cost=0.29..8.31 rows=1 width=8) (never executed)
Index Cond: (c_id = r.campaign_id)
Heap Fetches: 0
Planning time: 8393.337 ms
Execution time: 0.132 ms
(11 rows)
explain analyze select campaign_id , spent as spent from events_daily_20170720 r left join report_campaigns c on r.campaign_id = c.c_id where date >= '20170720' and date < '20170721' ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.29..20.57 rows=1 width=26) (actual time=0.008..0.008 rows=0 loops=1)
-> Seq Scan on events_daily_20170720 r (cost=0.00..12.25 rows=1 width=26) (actual time=0.007..0.007 rows=0 loops=1)
Filter: ((date >= '2017-07-20 00:00:00'::timestamp without time zone) AND (date < '2017-07-21 00:00:00'::timestamp without time zone))
-> Index Only Scan using report_campaigns_campaign_idx on report_campaigns c (cost=0.29..8.31 rows=1 width=8) (never executed)
Index Cond: (c_id = r.campaign_id)
Heap Fetches: 0
Planning time: 0.242 ms
Execution time: 0.059 ms
\d events_daily_20170720
date | timestamp without time zone |
Check constraints:
"events_daily_20170720_date_check" CHECK (date >= '2017-07-20 00:00:00'::timestamp without time zone AND date < '2017-07-21 00:00:00'::timestamp without time zone)
Inherits: events_daily
show constraint_exclusion;
constraint_exclusion
----------------------
on
When running ltrace it seems that it run this thousands of time on each field (hint that it run on all patitions tables for the plan) :
strlen("process") = 7
memcpy(0x0b7aac10, "process", 8) = 0x0b7aac10
strlen("channel") = 7
memcpy(0x0b7aac68, "channel", 8) = 0x0b7aac68
strlen("deleted") = 7
memcpy(0x0b7aacc0, "deleted", 8) = 0x0b7aacc0
strlen("old_spent") = 9
memcpy(0x0b7aad18, "old_spent", 10)