Re: understand query on partition table

[Samed YILDIRIM <samed@xxxxxxxxxx>]

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 ?