[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-bugs
Subject: Planner using wrong composite index with date interval statically calculated
From: Marco Carlo Moriggi <marco.moriggi () zucchetti ! it>
Date: 2020-07-29 10:37:39
Message-ID: b91bf7b86f3f4e09b40994a8f73fddf6 () zucchetti ! it
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Hi,
I'm writing for Zucchetti S.p.A.
We have a situation where a table has a small composite index e.g. (field1 \
varchar(10), field2 varchar(10)), and a wider index e.g. (field2 varchar(10), field1 \
varchar(10), field3 varchar(50), field4 date).
We added the second index in our production database to tune a query on a table with \
millions or records, checking that, when used like "select id from tbl where \
field1='fixed_val1' and field2='fixed_val2', and field3 = 'fixed_val3' and field4 \
between fixed_date1 and fixed_date2" The planner was using this new index.
In the practice we noticed that the planner was choosing the old one, anyway.
A closer look to the generated query showed that the date interval was not written as \
we expected by the application, but in this way: "select id from tbl where \
field1='fixed_val1' and field2='fixed_val2', and field3 = 'fixed_val3' and field4 \
between (substr('fixed_date1', 1, 4)||'-01-01')::date and (substr('fixed_date2', 1, \
4)|| '-12-31')::date"
Running explain analyse on both queries finally revealed that in this way the planner \
was using a first index scan on the old index, and then a sequential scan to filter \
the result set. (running for 3.5s instead of 0.496ms in our production database)
Anyway, the result of the two substring can be calculated once and then used in the \
exact same way of our first query. We placed a patch to generate the query in the \
right way, but I think it should be corrected also on the DB server query planner.
Attached to this email there's a test sequence of operation to reproduce the problem \
with random data.
Thanks in advance,
Marco
[Attachment #5 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"> <head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Calibri Light";
panose-1:2 15 3 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.StileMessaggioDiPostaElettronica17
{mso-style-type:personal-compose;
font-family:"Calibri Light",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 2.0cm 2.0cm 2.0cm;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="IT" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-family:"Calibri \
Light",sans-serif">Hi,<o:p></o:p></span></p> <p class="MsoNormal"><span \
lang="EN-US" style="font-family:"Calibri Light",sans-serif">I’m \
writing for Zucchetti S.p.A.<o:p></o:p></span></p> <p class="MsoNormal"><span \
lang="EN-US" style="font-family:"Calibri \
Light",sans-serif"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
lang="EN-US" style="font-family:"Calibri Light",sans-serif">We have a \
situation where a table has a small composite index e.g. (field1 varchar(10), field2 \
varchar(10)), and a wider index e.g. (field2 varchar(10), field1 varchar(10), field3 \
varchar(50), field4 date).<o:p></o:p></span></p> <p class="MsoNormal"><span \
lang="EN-US" style="font-family:"Calibri \
Light",sans-serif"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
lang="EN-US" style="font-family:"Calibri Light",sans-serif">We added the \
second index in our production database to tune a query on a table with millions or \
records, checking that, when used like <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Calibri \
Light",sans-serif">“</span><span lang="EN-US" \
style="font-family:Consolas">select id from tbl where field1=’fixed_val1’ \
and field2=’fixed_val2’, and field3 = ‘fixed_val3’ and field4 \
between fixed_date1 and fixed_date2</span><span lang="EN-US" \
style="font-family:"Calibri \
Light",sans-serif">”<o:p></o:p></span></p> <p class="MsoNormal"><span \
lang="EN-US" style="font-family:"Calibri Light",sans-serif">The planner was \
using this new index.<o:p></o:p></span></p> <p class="MsoNormal"><span lang="EN-US" \
style="font-family:"Calibri Light",sans-serif"><o:p> </o:p></span></p> \
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Calibri \
Light",sans-serif">In the practice we noticed that the planner was choosing the \
old one, anyway.<o:p></o:p></span></p> <p class="MsoNormal"><span lang="EN-US" \
style="font-family:"Calibri Light",sans-serif"><o:p> </o:p></span></p> \
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Calibri \
Light",sans-serif">A closer look to the generated query showed that the date \
interval was not written as we expected by the application, but in this \
way:<o:p></o:p></span></p> <p class="MsoNormal"><span lang="EN-US" \
style="font-family:"Calibri Light",sans-serif">“</span><span \
lang="EN-US" style="font-family:Consolas">select id from tbl where \
field1=’fixed_val1’ and field2=’fixed_val2’, and field3 = \
‘fixed_val3’ and field4 between (substr(‘fixed_date1’, 1, \
4)||’-01-01’)::date and (substr(‘fixed_date2’, 1, 4)|| \
‘-12-31’)::date</span><span lang="EN-US" style="font-family:"Calibri \
Light",sans-serif">”<o:p></o:p></span></p> <p class="MsoNormal"><span \
lang="EN-US" style="font-family:"Calibri \
Light",sans-serif"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
lang="EN-US" style="font-family:"Calibri Light",sans-serif">Running explain \
analyse on both queries finally revealed that in this way the planner was using a \
first index scan on the old index, and then a sequential scan to filter the result \
set. (running for 3.5s instead of 0.496ms in our production \
database)<o:p></o:p></span></p> <p class="MsoNormal"><span lang="EN-US" \
style="font-family:"Calibri Light",sans-serif"><o:p> </o:p></span></p> \
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Calibri \
Light",sans-serif">Anyway, the result of the two substring can be calculated \
once and then used in the exact same way of our first query. We placed a patch to \
generate the query in the right way, but I think it should be corrected also on the \
DB server query planner.<o:p></o:p></span></p> <p class="MsoNormal"><span \
lang="EN-US" style="font-family:"Calibri \
Light",sans-serif"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
lang="EN-US" style="font-family:"Calibri Light",sans-serif">Attached to \
this email there’s a test sequence of operation to reproduce the problem with \
random data.<o:p></o:p></span></p> <p class="MsoNormal"><span lang="EN-US" \
style="font-family:"Calibri Light",sans-serif"><o:p> </o:p></span></p> \
<p class="MsoNormal"><span lang="EN-US" style="font-family:"Calibri \
Light",sans-serif">Thanks in advance,<o:p></o:p></span></p> <p \
class="MsoNormal"><span lang="EN-US" style="font-family:"Calibri \
Light",sans-serif">Marco<o:p></o:p></span></p> </div>
</body>
</html>
["test.sql" (application/octet-stream)]
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic