Re: SourceForge & Postgres - Mailing list pgsql-hackers

Tom Lane wrote:
>
> mlw <markw@mohawksoft.com> writes:
> > cdinfo=# explain select * from ztitles where artistid = 0 ;
> > NOTICE:  QUERY PLAN:
>
> > Index Scan using ztitles_artistid_ndx on ztitles  (cost=0.00..5915.01
> > rows=3163 width=296)
>
> > When postmaster is started without "-o -fs" I get this:
>
> > cdinfo=# explain select * from ztitles where artistid = 0 ;
> > NOTICE:  QUERY PLAN:
>
> > Seq Scan on ztitles  (cost=0.00..4740.75 rows=3163 width=296)
>
> How many tuples are in the table?  How many are actually returned
> by this query?  Also, what do you get from
>
> select attname,attdisbursion,s.*
> from pg_statistic s, pg_attribute a, pg_class c
> where starelid = c.oid and attrelid = c.oid and staattnum = attnum
> and relname = 'ztitles';

I have attached the output.

btw anyone trying this query should use: "attdispersion"

The explain I gave, there are no records that actually have an artistid
of 0. However, I will show the explain with a valid artistid number.

This is without "-o -fs"
cdinfo=# explain select * from ztitles where artistid = 100000220 ;
NOTICE:  QUERY PLAN:

Seq Scan on ztitles  (cost=0.00..4740.75 rows=3163 width=296)

EXPLAIN

And this is with "-o -fs"

cdinfo=# explain select * from ztitles where artistid = 100000220 ;
NOTICE:  QUERY PLAN:

Index Scan using ztitles_artistid_ndx on ztitles  (cost=0.00..5915.01
rows=3163 width=296)

EXPLAIN


select count(*) from ztitles where artistid = 100000220 ;
 count
-------
    16
(1 row)

--
http://www.mohawksoft.com  attname   | attdispersion | starelid | staattnum | staop | stanullfrac | stacommonfrac |
stacommonval      |         staloval         |     stahival       

------------+---------------+----------+-----------+-------+-------------+---------------+------------------------+--------------------------+-------------------
 muzenbr    |            -1 |    19274 |         1 |    97 |           0 |   4.72277e-06 | 397705                 |
58608                   | 399022 
 artistid   |     0.0477198 |    19274 |         2 |    97 |           0 |      0.149362 | 100050450              |
100000000               | 100055325 
 cat2       |      0.618418 |    19274 |         3 |  1066 |           0 |      0.763058 | Performer              |
BoxedSet                | Single 
 cat3       |     0.0459786 |    19274 |         4 |  1066 | 4.72277e-06 |      0.145367 | International          |
Blues                   | Sound Effects 
 cat4       |      0.308324 |    19274 |         5 |  1066 | 4.72277e-06 |       0.50827 |                        |
                    | Zydeco 
 performer  |     0.0477281 |    19274 |         6 |  1066 | 4.72277e-06 |      0.149381 | Various Artists        |
"Blue"Gene Tyranny      | underGRIND 
 performer2 |     0.0477198 |    19274 |         7 |  1066 | 4.72277e-06 |      0.149362 | Various Artists        |
"Chuscales",Jose Valle  | underGRIND 
 title      |   1.88982e-05 |    19274 |         8 |  1066 | 4.72277e-06 |   9.44555e-05 | Good Old Country       | !
                    | Zzzzzz... [EP] * 
 artist1    |      0.770286 |    19274 |         9 |  1066 | 4.72277e-06 |      0.864182 |                        |
                    | w 
 engineer   |      0.719466 |    19274 |        10 |  1066 | 4.72277e-06 |      0.831534 |                        |
                    | ob Bullock 
 producer   |      0.586756 |    19274 |        11 |  1066 | 4.72277e-06 |      0.740488 |                        |
                    | Zvika Nadaf 
 labelname  |   0.000490215 |    19274 |        12 |  1066 | 4.72277e-06 |    0.00242751 | Rykodisc USA           |
                    | `A`A`Li`I Records 
 catalog    |    0.00114041 |    19274 |        13 |  1066 | 4.72277e-06 |    0.00557759 | 1                      |
                    | sftri325 
 distribut  |     0.0342314 |    19274 |        14 |  1066 | 4.72277e-06 |       0.11669 | Universal Distribution |
(Independentlyby Label) | n/a 
 released   |     0.0331312 |    19274 |        15 |  1066 | 4.72277e-06 |      0.113828 | n/a                    |
01/01/1986              | n/a 
 origrel    |     0.0266312 |    19274 |        16 |  1066 | 4.72277e-06 |     0.0961651 | 2000                   |
1911                    | n/a 
 nbrdiscs   |      0.931311 |    19274 |        17 |  1066 | 4.72277e-06 |      0.961169 | 1                      | 01
                    | 9 
 spar       |       0.84807 |    19274 |        18 |  1066 | 4.72277e-06 |      0.912166 | n/a                    | *N*
                    | n/a 
 minutes    |      0.778454 |    19274 |        19 |  1066 | 4.72277e-06 |       0.86933 |                        |
                    | 99 
 seconds    |      0.778454 |    19274 |        20 |  1066 | 4.72277e-06 |       0.86933 |                        |
                    | 98 
 monostereo |      0.854336 |    19274 |        21 |  1066 | 4.72277e-06 |      0.915935 | Stereo                 |
Mono                    | n/a 
 studiolive |      0.878293 |    19274 |        22 |  1066 | 4.72277e-06 |      0.930221 | Studio                 |
Live                    | n/a 
 available  |      0.632032 |    19274 |        23 |  1058 | 4.72277e-06 |      0.756938 | Y                      | N
                    | Y 
 previews   |      0.798323 |    19274 |        24 |  1066 | 4.72277e-06 |      0.881742 |                        |
                    | 99954 
 pnotes     |      0.310707 |    19274 |        25 |  1066 | 4.72277e-06 |      0.510617 |                        |
                    | 99986 
 acd        |      0.998235 |    19274 |        26 |  1066 | 4.72277e-06 |      0.999117 | A                      | A
                    | D 
(26 rows)