Re: understand query on partition table

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

 



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" <mariel.cherkassky@xxxxxxxxx>:
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