AW: [HACKERS] Really slow query on 6.4.2

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