understand query on partition table

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org, pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: understand query on partition table
Date: 2018-10-09 09:19:56
Message-ID: CA+t6e1nx78-FNAmH9FYiQqhUzTKHODHYi2HoTJztpJzzpzN7Tw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-performance

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 ?

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Samed YILDIRIM 2018-10-09 13:12:08 Re: understand query on partition table
Previous Message soumitra bhandary 2018-10-09 07:10:35 Re: Null value returned by function pg_last_wal_receive_lsn() inLogical Replication

Browse pgsql-performance by date

  From Date Subject
Next Message Samed YILDIRIM 2018-10-09 13:12:08 Re: understand query on partition table
Previous Message Justin Pryzby 2018-10-09 00:49:44 Re: Partial index plan/cardinality costing