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 ?
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 |
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 |