understand query on partition table

[Mariel Cherkassky]

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 ?

Re: understand query on partition table

[Samed YILDIRIM]

Dear Mariel, 1,4. Could you please check all child tables whether they all have check constraints or not? Does your main table store any data? Also could you please share output of following command.show constraint_exclusion; 2. Filtering on comments_daily_247 table over log_server_id is not big issue for your situation. Postgres applies filtering on your results because comments_daily_247_date_comment_id_idx composite index does not contain log_server_id. 3. I think it is not related with indexes. Best regards.İyi çalışmalar.Samed YILDIRIM   09.10.2018, 12:20, "Mariel Cherkassky" :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=# explainSELECT * FROM comments_dailywherelog_server_id in (247)ANDcomments_daily.date >= '2017-04-12'ANDcomments_daily.date <= '2017-04-12'ANDcomment_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 ?