AW: [HACKERS] Really slow query on 6.4.2
От | Zeugswetter Andreas IZ5 |
---|---|
Тема | AW: [HACKERS] Really slow query on 6.4.2 |
Дата | |
Msg-id | 219F68D65015D011A8E000006F8590C60267B331@sdexcsrv1.f000.d0188.sd.spardat.at обсуждение исходный текст |
Ответы |
Re: [HACKERS] Really slow query on 6.4.2
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: AW: [HACKERS] Really slow query on 6.4.2 (The Hermit Hacker <scrappy@hub.org>) |
Список | pgsql-hackers |
<br /><ul><p><font face="Fixedsys" size="2">Unfortunately, if you haven't done a vacuum, the system effectively</font><br/><font face="Fixedsys" size="2">assumes that all your tables are tiny. I think this is a brain-dead</font><br/><font face="Fixedsys" size="2">default, but haven't had much luck convincing anyone else that the</font><br/><font face="Fixedsys" size="2">default should be changed.</font></ul><p><font color="#0000FF" face="Fixedsys"size="2">I totally agree with Tom Lane here. Let me try to give some arguments.</font><br /> <br /><font color="#0000FF" face="Fixedsys" size="2">1.If you have a user that does vacuum analyze regularly, we can</font><br /><font color="#0000FF" face="Fixedsys"size="2">convince him to do vacuum analyze right after table creation, if he</font><br /><font color="#0000FF"face="Fixedsys" size="2">knows the table will be tiny.</font><p><font color="#0000FF" face="Fixedsys" size="2">2.We have an application where the size of 20 tables changes from </font><br /><font color="#0000FF" face="Fixedsys"size="2">0 to ~200000 rows in 3 hours. To have accurate statistics during the day we would need to analyzeat least every 20 min.</font><p><font color="#0000FF" face="Fixedsys" size="2">This was not acceptable during those3 hours.</font><br /><font color="#0000FF" face="Fixedsys" size="2">So we took the approach to tune the sql to workproperly without ever</font><br /><font color="#0000FF" face="Fixedsys" size="2">doing statistics.</font><br /><fontcolor="#0000FF" face="Fixedsys" size="2">This works perfectly on our Informix installation, since Informix has</font><br/><font color="#0000FF" face="Fixedsys" size="2">a tuning parameter, that tells it, that an index has to beused iff</font><br /><font color="#0000FF" face="Fixedsys" size="2">possible even if cost is higher, and the default fortable size is 100.</font><p><font color="#0000FF" face="Fixedsys" size="2">3. There are two types of popular optimizers,rule and cost based. </font><br /><font color="#0000FF" face="Fixedsys" size="2">A good approach is to behaverule based lacking statistics and cost</font><br /><font color="#0000FF" face="Fixedsys" size="2">based with statistics.An easy way to achieve this is to choose</font><br /><font color="#0000FF" face="Fixedsys" size="2">reasonabledefaults for the statistics before accurate statistics </font><br /><font color="#0000FF" face="Fixedsys"size="2">are made.</font><p><font color="#0000FF" face="Fixedsys" size="2">4. Those doing statistics willmost likely not leave out a few tables, thus creating an undefined state where the optimizer would behave rule</font><p><fontcolor="#0000FF" face="Fixedsys" size="2">and cost based.</font><p><font color="#0000FF" face="Fixedsys"size="2">5. Actually postgresql has behaved in this manner because of certain</font><br /><font color="#0000FF"face="Fixedsys" size="2">"bugs" in the optimizer. Recently a lot of those "bugs" have been</font><br /><fontcolor="#0000FF" face="Fixedsys" size="2">identified and "fixed", thus destroying the defacto rule based</font><br/><font color="#0000FF" face="Fixedsys" size="2">behavior.</font><p><font color="#0000FF" face="Fixedsys"size="2">If the defaults are not changed, behavior of the overall system will</font><br /><font color="#0000FF"face="Fixedsys" size="2">actually be changed for the case where statistics are lacking, when the</font><br/><font color="#0000FF" face="Fixedsys" size="2">optimizer is improved to actually behave cost based under all</font><br /><font color="#0000FF" face="Fixedsys" size="2">circumstances.</font><p><font color="#0000FF" face="Fixedsys"size="2">Andreas</font>
В списке pgsql-hackers по дате отправления: