From: =?iso-8859-2?Q?Piotr_W=B3odarczyk?= <piotr.wlodarczyk@gnb.pl>
To: "pgsql-performance@lists.postgresql.org"
	<pgsql-performance@lists.postgresql.org>
Subject: Planner performance in partitions
Thread-Topic: Planner performance in partitions
Thread-Index: AdVRCNnAOq9lhaGYSR2MYcANN/sz6Q==
Date: Mon, 12 Aug 2019 12:37:19 +0000
Message-ID: <5675f9f771f04d9f8b5cffe16f4a4175@EX13MBX02.gb.ad.corp>
Accept-Language: pl-PL, en-US
Content-Language: pl-PL
dlp-product: dlpe-windows
dlp-version: 11.2.0.14
dlp-reaction: no-action
Content-Type: multipart/signed; protocol="application/x-pkcs7-signature";
	micalg=SHA1; boundary="----=_NextPart_000_0104_01D5511B.70D5BC00"
MIME-Version: 1.0
Precedence: bulk

------=_NextPart_000_0104_01D5511B.70D5BC00
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: 7bit

Hello,

We have partitioned tables in two levels. Both stages are partitioned in
ranges method. We see that planner and executor time was 10 time slower when
we asked main table rather than partitioned. My question is did planner and
executor are working optimal? I have doubts about it. Let's consider that
situation, because I think that in some ways (I think in more cases) planner
shoudl be more optimal. When we have table partitioned by key which is in
"where" clause we have guarantee that all rows we can find just in ONE
partition: contained in range or in default if exists. Planner show that
query should be executed just in one partition, but it takes a lot of time.
So my direct question is if planner is stopping searching for another
partition when he found one correct? Are partition ranges stored sorted and
searching method is optimal and is stopped after first (and only) hit?

I've noticed that increasing numbers of partition proportionally increase
planner time. Additionally having index on column that you are searching for
is adding extra time (one index add +/- 100% time for table). It's
understandable but optimizing planner and executor by ideas I wrote on first
paragraph automatically decrease time for searching indexes.

Reproduction:
1. ADD MAIN TABLE

-- Table: public.book

--DROP TABLE public.book;

CREATE TABLE public.book
(
    id bigserial,
    id_owner bigint NOT NULL,
    added date NOT NULL
) PARTITION BY RANGE (id_owner) 
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.book
    OWNER to postgres;

2. ADD PARTITIONS (run first "a" variant, then drop table book, reconstruct
and run "variant"):
    a. 1200 partitions:
https://gist.github.com/piotrwlodarczyk/4faa05729d1bdd3b5f5738a2a3faabc0 
    b. 6000 partitions:
https://gist.github.com/piotrwlodarczyk/2747e0984f521768f5d36ab2b382ea36 
    
3. ANALYZE ON MAIN TABLE:
    EXPLAIN ANALYZE SELECT * FROM public.book WHERE id_owner = 4;
    a. My result for 1200 partitions:
https://gist.github.com/piotrwlodarczyk/500f20a0b6e2cac6d36ab88d4fea2c00 
    b. My result for 6000 partitions:
https://gist.github.com/piotrwlodarczyk/277687b21201340377116a18a3dd8be8

4. ANALYZE ON PARTITIONED TABLE (only on first level):
  EXPLAIN ANALYZE SELECT * FROM public.book WHERE id_owner = 4;
  a. My result for 1200:
https://gist.github.com/piotrwlodarczyk/4285907c68b34b486cbf39eb8ae5cf92
  b. My result for 6000:
https://gist.github.com/piotrwlodarczyk/c157cc9321b6e1a1d0f900310f14f1cc

4. CONCLUSIONS
    Planner time for select on public.book (main table) 1200 was 469.416 ms,
for 6000 was 2530.179 ms. It looks like time is linear to partition count.
That makes me sure that all partitions are checked instead of searching for
first that equals. Intentionally I've searching id_owner = 4 to make sure
that in both cases first partition should by marked as correct and planer
time should be constant. What is intereting too that real execution time was
+/- equal in both cases. Is executor working better than planner?
  When we're asking on first level partition directly - time for planner
1200 is 58.736 ms, for 6000: 60.555 ms. We can say it's equal. Why? Because
planner don't have to search for another matching partitions because first
found can match. It's guaranteed by rule that say ranges in partitions
cannot override. Execution time in this case is 50 times faster! 


------=_NextPart_000_0104_01D5511B.70D5BC00
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIISejCCBTow
ggMioAMCAQICEBm1SlhGhyG6TItEbUzqtdMwDQYJKoZIhvcNAQELBQAwHTEbMBkGA1UEAxMSR0VU
SU4gQmFuayBSb290IENBMB4XDTA2MTExNjA5NDU1NVoXDTM2MTIxNDE2MTgyNVowHTEbMBkGA1UE
AxMSR0VUSU4gQmFuayBSb290IENBMIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEAsF1q
8IHZ5O9M0JAJ+oPRzb4tuWxSeusQeRpKD9wC6CKQIYhxUn4IpZ+uLUUlu+hDXMw2fMtmFC10hc51
YO/OyN7eju5fkpWS83rR/TD8AQeRsHf8tFQZmCa4Q9ihZzoBR620s+D1Naf0sJP/AfCktEoFEnNn
HUg5z/OvTHQqZ/FXJQbSBIE/rq59pNoapJZZyvv7qgeQp7sGWqUN/YHlLgG9UnRlO+kWOoRphI9G
w1leTCTq20joZ4STj5D0YLWMRkR8dV3a6zuWJw/U0t8TThsSEDmMBNVj6z8VqtT2IXwqU0z6Og0Y
ibotl0VW8OyQ4QCwEUxzQnVtHdpQMeVvM+HFzxo7vgg3yhio59sXE61c6cFIix0owLLfteAj6w64
/60rjVEEo+COZo3amklBeZjp03xwoYBSV1GJJVOiaVvBv2i4IYP1aQnHOEGbufFcbhIxSDGNvxNN
VsIf8gl0lRP4T5x+mLQ+ZWJktCe+sRJ6vKPq7Zb9Tr2/PVPWs6cQzp/I9Ku2kFD5laXW6h+qxn21
wRw8S7swJGbJXCeYA0ehXc209MP+e6BKkSerziA5ePKUWLwgP0qE2sLxI+608vH3wBuVJq44zid6
wjvU2QnCLaonn2NJH7doZS+Hgw9tt9DNmbjHwF8kba5sKsrjOMtpF8F325qyq5CebZXXbqUCAwEA
AaN2MHQwCwYDVR0PBAQDAgGGMA8GA1UdEwEB/wQFMAMBAf8wHQYDVR0OBBYEFFpbOoxPlrUmNhn/
qjJPFMJDOzy3MBAGCSsGAQQBgjcVAQQDAgEBMCMGCSsGAQQBgjcVAgQWBBRVWJycPs6ZLMSUvMM2
IZNU5GdPMjANBgkqhkiG9w0BAQsFAAOCAgEATBQx/8TtuXKUOW+7mmg+LuhBjgE77mTn/Gsdva1X
jemnoCO3aSapLDZvjWIqkOahgmBhDkIlOsfFnoLps5S7qjckRcFSd6uypIPouj2crRaWCNMFFWFx
VY0fDZELHCsKPKN/K1qPNbS2JmYbengFYKEe//SVmpSm099ulmQDMxpNKSKh1BGn5ghg3VHny+Jm
cqlSbTOBZ2DCbeHzPxMfgG3OtVjS/GthoUkEKgWMsrTMo3TbFzgaabuopEYczzwhkxW9L3GilHea
a5mTrTKT8mus48Ef8qJawkzGn7f9RmTl0pYnMTMLOOnksTyQ1llwFtfrzIQX4oq1jNN4aQI1Nsb8
Q6eGmkcuVEBN4ZmXEwW6BLv+GuAFMr5WR/QjIzlW/0uUXEifuKtyp9hH7zDAO3N11xWs5DcbDV9N
aY5i6gxValXVeaW4KAC4EjrK1Ll2aRgyFtLU9paaob/s6oAR1ayOFtLCwIqXleOHS15I2n6cq/o7
i9H1ZPLVaHVi6c31ekpmZmnBju3HUpunwNG2bMpG3gIlzYwadPyBIB/vJud2VAj3sAPl8rqyVtVL
+BlqObHFXUIZZMlgS3Ke9+Md7Z4G2Qdm3tk1UYfa2n/c1yiZta4MwP0kP1fQVRLpvJw4W71L/bwW
ihFP4WZBTeqR8gOjE+nyXuIdhfk0vFbAijMwggZwMIIFWKADAgECAhMqAACG/jfeXRw8ln4dAAMA
AIb+MA0GCSqGSIb3DQEBCwUAMFkxFDASBgoJkiaJk/IsZAEZFgRjb3JwMRIwEAYKCZImiZPyLGQB
GRYCYWQxLTArBgNVBAMTJEdFVElOIEJhbmsgU3ViIENBIFBLSUFETTAyIC0gQ2xhc3MgMTAeFw0x
OTAxMDIxMjU2MDNaFw0yMTAxMDExMjU2MDNaMFMxKTAnBgNVBAMMIFfFgm9kYXJjenlrIFBpb3Ry
IERlcGFydGFtZW50IElUMSYwJAYJKoZIhvcNAQkBFhdwaW90ci53bG9kYXJjenlrQGduYi5wbDCB
nzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAw/WQ+Xt7vli5/RmVQi3Va27PJX3+1xB4hL6NC/Bs
YPhN+BZfcs3NG46vTZFbBFSompfBkByS9T8p3rjvEp7Jm5k8Um5J6Oh2t4t/WAoBZTGckIDQSBuC
YyHMAX6+GNu1f+quFC88SKngMIuF10yYiwSqwLTvmR0V52Sj4+otW7ECAwEAAaOCA7kwggO1MAsG
A1UdDwQEAwIEsDA9BgkrBgEEAYI3FQcEMDAuBiYrBgEEAYI3FQiDyLg1hLebEIa1lyWG2dd65NwC
gTaDj74lhaCicwIBZAIBGjBEBgkqhkiG9w0BCQ8ENzA1MA4GCCqGSIb3DQMCAgIAgDAOBggqhkiG
9w0DBAICAIAwBwYFKw4DAgcwCgYIKoZIhvcNAwcwHQYDVR0lBBYwFAYIKwYBBQUHAwIGCCsGAQUF
BwMEMCcGCSsGAQQBgjcVCgQaMBgwCgYIKwYBBQUHAwIwCgYIKwYBBQUHAwQwSwYDVR0RBEQwQqAn
BgorBgEEAYI3FAIDoBkMF1Bpb3RyLldsb2RhcmN6eWtAZ25iLnBsgRdwaW90ci53bG9kYXJjenlr
QGduYi5wbDAdBgNVHQ4EFgQUf3+6rUH7lWREU04dYOJYPOXvodYwHwYDVR0jBBgwFoAUx+bj0g3o
gN5f+oYufXdw+Q5g46swggEoBgNVHR8EggEfMIIBGzCCARegggEToIIBD4aB2WxkYXA6Ly8vQ049
R0VUSU4lMjBCYW5rJTIwU3ViJTIwQ0ElMjBQS0lBRE0wMiUyMC0lMjBDbGFzcyUyMDEoMyksQ049
UEtJQURNMDIsQ049Q0RQLENOPVB1YmxpYyUyMEtleSUyMFNlcnZpY2VzLENOPVNlcnZpY2VzLENO
PUNvbmZpZ3VyYXRpb24sREM9YWQsREM9Y29ycD9jZXJ0aWZpY2F0ZVJldm9jYXRpb25MaXN0P2Jh
c2U/b2JqZWN0Q2xhc3M9Y1JMRGlzdHJpYnV0aW9uUG9pbnSGMWh0dHA6Ly9wa2kuZ25iLnBsL0Nl
cnRFbnJvbGwvR05CX1NVQl9DQV8wMigzKS5jcmwwggEeBggrBgEFBQcBAQSCARAwggEMMIHNBggr
BgEFBQcwAoaBwGxkYXA6Ly8vQ049R0VUSU4lMjBCYW5rJTIwU3ViJTIwQ0ElMjBQS0lBRE0wMiUy
MC0lMjBDbGFzcyUyMDEsQ049QUlBLENOPVB1YmxpYyUyMEtleSUyMFNlcnZpY2VzLENOPVNlcnZp
Y2VzLENOPUNvbmZpZ3VyYXRpb24sREM9YWQsREM9Y29ycD9jQUNlcnRpZmljYXRlP2Jhc2U/b2Jq
ZWN0Q2xhc3M9Y2VydGlmaWNhdGlvbkF1dGhvcml0eTA6BggrBgEFBQcwAoYuaHR0cDovL3BraS5n
bmIucGwvQ2VydEVucm9sbC9HTkJfU1VCX0NBXzAyLmNydDANBgkqhkiG9w0BAQsFAAOCAQEAc6hN
CeqlY+tueVdnzc2y+FI0imauRq4I24hyPB5DI13MlQYGGt4lADxk+jixvU8ODIbjuGVLfIAhMGef
IS6kF4+nsSNQLKU3dKDckUv0r5zipxGrLN1EGe/FXdofm8vXS28ynIeOUBB68MLkL1Sye+ngE5OH
oZOtRdsRX22eK3nTDiqQN+A/hFUUc2Aq/Te5fl/mT+v7T6QT7De7UIWcZiYW4DVXM8kFm1RQU0wP
WHMkG3uxW4X78kXDSi9GJeciQK7HtIM9+wKTh9ZEvwJmOCyYwRJX/B4cJHyR8R5MR9++i9Ok7AEd
IsYvNnlO1ONxjeEioGXcxIPNCkrfmKVEWTCCBsQwggSsoAMCAQICE3AAAAATDt1X0RJgBgoAAQAA
ABMwDQYJKoZIhvcNAQELBQAwHTEbMBkGA1UEAxMSR0VUSU4gQmFuayBSb290IENBMB4XDTE2MTIx
NDE3MzUyNVoXDTI2MTIxNDE3NDUyNVowWTEUMBIGCgmSJomT8ixkARkWBGNvcnAxEjAQBgoJkiaJ
k/IsZAEZFgJhZDEtMCsGA1UEAxMkR0VUSU4gQmFuayBTdWIgQ0EgUEtJQURNMDIgLSBDbGFzcyAx
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAonUsdYlmhH36J4TrT4ZQTv/sgTvuDwHL
3fYtVxcTUtev5DnmMs6K7w9elWtCk2yhXNxiJDIFTAoMjI6eLhWSTxVmXe7OIkk8AW0Ppw4idbp0
YfZZDyQ5d9EKoLKOgpJe4t9ZkQTfRFGGePasqwwUl+5KXvHwMFuwvDuMHqVf/rn6maUQ2knG5IAe
d0e3x4DJW1GT6/RtqDFmp3G2aZLtSLFx3xSXIamI8cscJBmsK9dqEH0lDRGUXE4LOwvpCGSQYFS7
/gzWRxfPAshGRgFZfDkb4i+bK29NBfe3tcd+dTNgH99vEE1uIAVQfEFauMlqNF3Td18KA4tMujEX
q7GH9wIDAQABo4ICvzCCArswEgYJKwYBBAGCNxUBBAUCAwMAAzAjBgkrBgEEAYI3FQIEFgQUlISC
MWNRx1ZZ3DZYnRzTC5q+GQkwHQYDVR0OBBYEFMfm49IN6IDeX/qGLn13cPkOYOOrMBkGCSsGAQQB
gjcUAgQMHgoAUwB1AGIAQwBBMAsGA1UdDwQEAwIBhjAPBgNVHRMBAf8EBTADAQH/MB8GA1UdIwQY
MBaAFFpbOoxPlrUmNhn/qjJPFMJDOzy3MIIBAQYDVR0fBIH5MIH2MIHzoIHwoIHthoG8bGRhcDov
Ly9DTj1HRVRJTiUyMEJhbmslMjBSb290JTIwQ0EsQ049UEtJQURNMDEsQ049Q0RQLENOPVB1Ymxp
YyUyMEtleSUyMFNlcnZpY2VzLENOPVNlcnZpY2VzLENOPUNvbmZpZ3VyYXRpb24sREM9YWQsREM9
Y29ycD9jZXJ0aWZpY2F0ZVJldm9jYXRpb25MaXN0P2Jhc2U/b2JqZWN0Q2xhc3M9Y1JMRGlzdHJp
YnV0aW9uUG9pbnSGLGh0dHA6Ly9wa2kuZ25iLnBsL0NlcnRFbnJvbGwvR05CX1Jvb3RfQ0EuY3Js
MIIBAAYIKwYBBQUHAQEEgfMwgfAwgbMGCCsGAQUFBzAChoGmbGRhcDovLy9jbj1HRVRJTiUyMEJh
bmslMjBSb290JTIwQ0EsY249QUlBLGNuPVB1YmxpYyUyMEtleSUyMFNlcnZpY2VzLGNuPVNlcnZp
Y2VzLGNuPUNvbmZpZ3VyYXRpb24sZGM9YWQsZGM9Y29ycD9jQUNlcnRpZmljYXRlP2Jhc2U/b2Jq
ZWN0Q2xhc3M9Y2VydGlmaWNhdGlvbkF1dGhvcml0eTA4BggrBgEFBQcwAoYsaHR0cDovL3BraS5n
bmIucGwvQ2VydEVucm9sbC9HTkJfUm9vdF9DQS5jcnQwDQYJKoZIhvcNAQELBQADggIBACU+Z5tp
WX+tKjW/sL18LWackZemcktt0pmPLXPlcgCb6Cjfb5kEy+hdqIn8t9H7zJRJQt8ygAzSQ70P1pTp
W/1YYk8BjGhnZsqyhMbw1SCJ40BYTnOKhw2duI7sJU5hapdii414Ajyu0yfQOT3qmRtPph0xl/EQ
WV8/J7yqdrl/oGBKOiMGjQ0vW6Rr+2pEKWti6K26P8+yP8XpGL65r5D3M3YT2OhVwAH0qpACyfEn
RnVMPQSCXAZhAHn3kaW0sC9+PmYm8kwpcWh3u/iiuNB5V7SNbhGeZmJmv40pbh9/meCR6x6kJuV/
UZ7ReW0W8ZNAowHxalXjDNtNizGrPmZrj7h/BAOYWMDIg15J4RHTw+NUFqK6eG9tGXPKVqOuP8Sa
6sa9ows4npYeFVrNlK+hnY1Je9HcE27WVwPlryagoLq3/6R+/G0OxPzC4edeY+qah8V0teyYyAqh
OFqArQF0tByt49THVBtlBLW1zyuFaqpyXxweRaL5QdFXB+JctTmKQo8zsnHCj8m2W5AfrIYtVcpV
KeCuAKMhVjE76CkQ20+1OoVxCfNfbitcHaKNEY/DcPSyPU9klyOF2eh9gKZVxNmR2jOkVuBNjnOG
p0hTjr0v2yduLlMqRvRpPmpKIbPkITFV9V13oIpyQH5uwjKklF3iOZjJTD7UiKwZJRM9MYIDKjCC
AyYCAQEwcDBZMRQwEgYKCZImiZPyLGQBGRYEY29ycDESMBAGCgmSJomT8ixkARkWAmFkMS0wKwYD
VQQDEyRHRVRJTiBCYW5rIFN1YiBDQSBQS0lBRE0wMiAtIENsYXNzIDECEyoAAIb+N95dHDyWfh0A
AwAAhv4wCQYFKw4DAhoFAKCCAhAwGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0B
CQUxDxcNMTkwODEyMTIzNzE3WjAjBgkqhkiG9w0BCQQxFgQUe8NnJqeU2HVh7+XZ/8yqIfA7feMw
fwYJKwYBBAGCNxAEMXIwcDBZMRQwEgYKCZImiZPyLGQBGRYEY29ycDESMBAGCgmSJomT8ixkARkW
AmFkMS0wKwYDVQQDEyRHRVRJTiBCYW5rIFN1YiBDQSBQS0lBRE0wMiAtIENsYXNzIDECEyoAAIb+
N95dHDyWfh0AAwAAhv4wgYEGCyqGSIb3DQEJEAILMXKgcDBZMRQwEgYKCZImiZPyLGQBGRYEY29y
cDESMBAGCgmSJomT8ixkARkWAmFkMS0wKwYDVQQDEyRHRVRJTiBCYW5rIFN1YiBDQSBQS0lBRE0w
MiAtIENsYXNzIDECEyoAAIb+N95dHDyWfh0AAwAAhv4wgasGCSqGSIb3DQEJDzGBnTCBmjAKBggq
hkiG9w0DBzALBglghkgBZQMEASowCwYJYIZIAWUDBAEWMAsGCWCGSAFlAwQBAjAOBggqhkiG9w0D
AgICAIAwBwYFKw4DAgcwDQYIKoZIhvcNAwICAUAwDQYIKoZIhvcNAwICASgwBwYFKw4DAhowCwYJ
YIZIAWUDBAIDMAsGCWCGSAFlAwQCAjALBglghkgBZQMEAgEwDQYJKoZIhvcNAQEBBQAEgYCDvo7q
h0TlSWTfqR5vwbfvQcMQ27ixqSgMdtSAKGVoC3zXYmUKs9N1rgBX/X/gehuEABbxoa9w9Kr89qGA
Vjo/dS1fSt2KG1Omj8FWVUKE7ndX5PTb428WdR1HIMJzmvzUmt08xrJrmUgYCQVWA0KCyvHuBNfU
EOM8S4s3eBXp1AAAAAAAAA==

------=_NextPart_000_0104_01D5511B.70D5BC00--