[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: postgresql 10.1 scanning all partitions instead of 1
From: Mariel Cherkassky <mariel.cherkassky () gmail ! com>
Date: 2018-02-04 9:28:47
Message-ID: CA+t6e1nJJABa1emeNHhk85GymSOM5hqV3GO720wJWUSZgDg9dw () mail ! gmail ! com
[Download RAW message or body]
Sorry it didnt send the whole mail :
Hi,
I configured range partitions on a date column of my main table(log_full).
Each partition represents a day in the month. Every day partition has a
list parition of 4 tables on a text column.
log_full
log_full_01_11_2017 -->
log_full _01_11_2017_x1
log_full _01_11_2017_x2
log_full _01_11_2017_x3
log_full _01_11_2017_x4
log_full_02_11_2017
log_full _02_11_2017_x1
log_full _02_11_2017_x1
log_full _02_11_2017_x1
log_full _02_11_2017_x1
and so on....
The date column consist of date in the next format : YYYY-MM-DD HH:24:SS
for example : 2017-11-01 00:01:40
I wanted to check the plan that I'm getting for a query that is using the
date column and it seems that the planner choose to do seq scans on all
tables.
-Each partition consist from 15M rows.
I have about 120 partitions.
The query :
explain select count(*) from log_full where end_date between
to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
The output is too long but it do full scans on all paritions...
any idea what can be the problem? Is it connected to the date format ?
Thanks , Mariel.
2018-02-04 11:23 GMT+02:00 Mariel Cherkassky <mariel.cherkassky@gmail.com>:
> Hi,
> I configured range partitions on a date column of my main
> table(full_table). Each partition represents a day in the month. Every day
> partition has a list parition of 4 tables on a text column.
>
> full table
> table_01_11_2017 -->
>
> table_02_11_2017
> .....
>
[Attachment #3 (text/html)]
<div dir="rtl"><div dir="ltr">Sorry it didnt send the whole mail : </div><div \
dir="ltr">
<div dir="ltr" style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8p \
x;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight \
:400;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white- \
space:normal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">Hi,</div><div \
dir="ltr" style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;fon \
t-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400; \
letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space \
:normal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">I \
configured range partitions on a date column of my main table(log_full). Each \
partition represents a day in the month. Every day partition has a list parition of 4 \
tables on a text column.</div><div dir="ltr" \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><br></div><div \
dir="ltr" style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;fon \
t-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400; \
letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space \
:normal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">log_full</div><div \
dir="ltr" style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;fon \
t-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400; \
letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space \
:normal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"> \
log_full_01_11_2017 --></div><div \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"> \
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">log_full</span>
_01_11_2017_x1</div><div \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"> \
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">log_full</span>
_01_11_2017_x2</span><br></div><div \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"> \
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">log_full</span>
_01_11_2017_x3</span>
<br></div><div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8p \
x;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight \
:400;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white- \
space:normal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"> \
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">log_full</span>
_01_11_2017_x4</span>
<br></div><div dir="ltr" \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"> \
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;t \
ext-decoration-color:initial;float:none;display:inline">log_full</span>_02_11_2017</div><div \
dir="ltr" style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;fon \
t-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400; \
letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space \
:normal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"> \
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">log_full</span>
_02_11_2017_x1</span>
<br></div><div dir="ltr" \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"> \
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">log_full</span>
_02_11_2017_x1</span>
<br></div><div dir="ltr" \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"> \
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">log_full</span>
_02_11_2017_x1</span>
<br></div><div dir="ltr" \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"> \
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-st \
yle:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;lett \
er-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:nor \
mal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">log_full</span>
_02_11_2017_x1</span></div><div dir="ltr" \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">
<br></div><div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8p \
x;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight \
:400;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white- \
space:normal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">and \
so on....</div><div dir="ltr" \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"> \
</div><div dir="ltr" \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><br></div><div \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">The \
date column consist of date in the next format : YYYY-MM-DD HH:24:SS for example : \
2017-11-01 00:01:40</div><div \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><br></div><div \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">I \
wanted to check the plan that I'm getting for a query that is using the date \
column and it seems that the planner choose to do seq scans on all tables.</div><div \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><br></div><div \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">-Each \
partition consist from 15M rows.</div><div \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">I \
have about 120 partitions.</div><div \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><br></div><div \
style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:no \
rmal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spa \
cing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;wo \
rd-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">The \
query : </div><div style="text-align:start;text-indent:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><span \
style="font-size:12.8px">explain select count(*) from log_full where end_date between \
to_date('2017/12/03','YY/MM/DD') and \
to_date('2017/12/03','YY/MM/DD');</span><br></div><div \
style="text-align:start;text-indent:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><span \
style="font-size:12.8px"><br></span></div><div \
style="text-align:start;text-indent:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><span \
style="font-size:12.8px">The output is too long but it do full scans on all \
paritions...</span></div><div \
style="text-align:start;text-indent:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">any \
idea what can be the problem? Is it connected to the date format ?</div><div \
style="text-align:start;text-indent:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><br></div><div \
style="text-align:start;text-indent:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">Thanks \
, Mariel.</div><div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size: \
12.8px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-w \
eight:400;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;w \
hite-space:normal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><br></div>
<br></div></div><div class="gmail_extra"><div dir="ltr"><br><div \
class="gmail_quote">2018-02-04 11:23 GMT+02:00 Mariel Cherkassky <span \
dir="ltr"><<a href="mailto:mariel.cherkassky@gmail.com" \
target="_blank">mariel.cherkassky@gmail.com</a>></span>:<br><blockquote \
class="gmail_quote" style="margin:0 .8ex;border-left:1px #ccc solid;border-right:1px \
#ccc solid;padding-left:1ex;padding-right:1ex"><div dir="rtl"><div \
dir="ltr">Hi,</div><div dir="ltr">I configured range partitions on a date column of \
my main table(full_table). Each partition represents a day in the month. Every day \
partition has a list parition of 4 tables on a text column.</div><div \
dir="ltr"><br></div><div dir="ltr">full table </div><div dir="ltr"> \
table_01_11_2017 --></div><div dir="ltr"><br></div><div dir="ltr"> \
table_02_11_2017</div><div dir="ltr"> .....</div></div> \
</blockquote></div></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic