understand query on partition table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 ?

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux