Re: optimizer

On Wed, 27 Feb 2002, Zhang, Anna wrote:

>
> >Is the estimate above (1.5M rows) reasonable?  If so, it's probably
> >doing the right thing.  If not, what version are you using and are
> >there any very common values that may throw off the estimates; what
> >does select * from pg_statistic where starelid=(select oid from
> >pg_class where relname='domain'); give?

Okay, does running it after set enable_seqscan=false; actually run faster
than the sequence scan version?

> select * from pg_statistic where starelid=(select oid from pg_class where
> relname='domain');
>
> starelid  | staattnum | staop | stanullfrac | stacommonfrac |
> stacommonval        |           staloval           |
>
>     stahival
> -----------+-----------+-------+-------------+---------------+--------------
> --------------+------------------------------+------------------------------
> -----------------------------------
> 749413081 |         1 |   664 |           0 |   7.02145e-08 | UPSPWR-DOM
>
>              | 000000000000000000000-N3-DOM | ZZZZZZZZZZZZZZZZZZZZZ9-DOM
> 749413081 |         2 |   664 |           0 |   7.02145e-08 | UPSPWR
>
>              | 00                           |
> ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
> 749413081 |         3 |   664 |           0 |      0.728349 | COM
>
>              | ARPA                         | WS
> 749413081 |         4 |   664 |           0 |      0.107595 | NAMEZERO.COM
>
>              | `                            |    ambassador blinds
> 749413081 |         5 |   664 |           0 |      0.107594 | 51 University
> Ave, Suite K | -                            | zzzzzzzzzzzzzzzzzz
> zzzzzzzzzzzzzzzz, zzzzzzzzzzz 325698
> 749413081 |         6 |   664 |    0.312378 |      0.107716 | LOS GATOS
>
>              | -                            |  Murray
> 749413081 |         7 |   664 |    0.312378 |      0.227842 | CA
>
>              | AA                           | WY
> 749413081 |         8 |   664 |    0.312119 |      0.107774 | 95030
>
>              | 00005                        | 99995
> 749413081 |         9 |   664 |  0.00980173 |      0.687646 | US
>
>              | AC                           | ZW
> (9 rows)
>
> The column holdername is not common, same holdername may have a few records.
> I am running postgres 7.2, for pg_statistic table I really no idea what each

Are you *sure* this is a 7.2 server?  The above looks like the form from
7.1 and earlier.  In any case, it looks like NAMEZERO.COM is the most
common value with about 10% of the table, so I don't think sequence scan
is a bad plan necessarily.