Hi,
I'm trying to understand the execution plan that is chosen for my query when I run a select on a partition table . I have on my main partition table rules that redirect the insert to the right son table.
My scheme :
Postgresql 9.6.8
mydb=# \d comments_daily
Table "public.fw_log_daily"
Column | Type | Modifiers
---------------+-----------------------+-----------
log_server_id | bigint | not null
comment_id | bigint | not null
date | date | not null
Rules:
comments_daily_1 AS
ON INSERT TO fw_log_daily
WHERE new.log_server_id = 1::bigint DO INSTEAD INSERT INTO comments_daily_1 (log_server_id,comment_id, date)
VALUES (new.log_server_id, new.comment_id, new.date)
comments_daily_2 AS
ON INSERT TO fw_log_daily
WHERE new.log_server_id = 1::bigint DO INSTEAD INSERT INTO comments_daily_2 (log_server_id, comment_id, date)
VALUES (new.log_server_id, new.comment_id, new.date)
and so on...
The son table structure :
mydb=# \d comments_daily_247
Table "public.comments_daily_247"
Column | Type | Modifiers
---------------+-----------------------+-----------
log_server_id | bigint | not null
comment_id | bigint | not null
date | date | not null
Indexes:
"comments_daily_247_date_device_id_idx" btree (date, device_id)
Check constraints:
"comments_daily_247_log_server_id_check" CHECK (log_server_id = 247::bigint)
Inherits: comments_daily
the query :
mydb=# explain
SELECT * FROM comments_daily
where
log_server_id in (247)
AND
comments_daily.date >= '2017-04-12'
AND
comments_daily.date <= '2017-04-12'
AND
comment_id IN (1256);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..47368.49 rows=2 width=186)
-> Seq Scan on comments_daily (cost=0.00..47360.30 rows=1 width=186)
Filter: ((date >= '2017-04-12'::date) AND (date <= '2017-04-12'::date) AND (log_server_id = 247) AND (comment_id = 1256))
-> Index Scan using comments_daily_247_date_comment_id_idx on comments_daily_247 (cost=0.15..8.19 rows=1 width=186)
Index Cond: ((date >= '2017-04-12'::date) AND (date <= '2017-04-12'::date) AND (comment_id = 1256))
Filter: (log_server_id = 247)
(6 rows)
traffic_log_db=#
I had 2 questions :
1)Why the filtering on the main comments_daily table is according to all the where clause and not only according the log_server_id?
2)Why the filtering on the son table is according to the log_server_id ? Is it because of the check constraint ?
3)Should I create another index to improve the performance ?
4)Any suggestions ?
