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 ?