Re: Optimizer(?) off by factor of 3 ... ?

On Mon, 11 Feb 2002, Tom Lane wrote:

> "Marc G. Fournier" <scrappy(at)hub(dot)org> writes:
> > -> Index Scan using clubs_idx on clubs c (cost=0.00..1695474.62 rows=26569 width=64) (actual time=0.48..1936.95 rows=23510 loops=1)
>
> This indexscan cost estimate is completely out of whack, it would seem.
>
> Have you done an ANALYZE on this table recently? If so, what do you get

Yup, been doing ANALYZEs just to make sure that I did them, so have done
several since this database/table was populated ...

> from
> select * from pg_stats where tablename = 'clubs';
> select * from pg_class where relname = 'clubs';

tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | !
histogram_bounds !
| correlation
-----------+---------------+-----------+-----------+------------+-----------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------!
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------!
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
clubs | uid | 0 | 8 | -1 | | | {13,56847,365368,432334,482114,538111,627969,683193,738091,793220,841391} !
!
| 0.596839
clubs | club | 0 | 4 | 3 | {2,1,3} | {0.754,0.195333,0.0506667} | !
!
| 1
clubs | hide | 0 | 2 | 2 | {1,0} | {0.950667,0.0493333} | !
!
| 0.810325
clubs | last_update | 0 | 8 | 7731 | {1008005872,1009714469,1009688701,1011503100,1011330301,1009256700,1009429504,1011848704,1009885559,1010207101} | {0.735,0.004,0.00266667,0.00233333,0.002,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333} | {1007584989,1008125462,1008569460,1009199787,1009651136,1010099882,1010466300,1010887647,1011224456,1011537512,1011853900} !
!
| 0.691723
clubs | category | 0 | 30 | 7 | {"{1,0,0,0,5}","{0,0,0,4,0}","{0,0,0,0,0}","{0,0,0,4,5}","{1,2,0,0,0}","{1,2,3,4,5}","{1,2,3,4,0}"} | {0.316333,0.268667,0.169,0.139333,0.056,0.0256667,0.025} | !
!
|
clubs | club_interest | 0 | 44 | 1 | {"{0,0,0,0,0,0,0,0,0,0,0,0}"} | {1} | !
!
|
clubs | headline | 0 | 28 | 27663 | {"","looking for fun",Hello,hi,Hi,hello,Looking,"Looking for fun",hey,"Hello Ladies"} | {0.103,0.00566667,0.004,0.004,0.00366667,0.00333333,0.00166667,0.00166667,0.00166667,0.00133333} | {" ANGEL EYES","Cum get sum","Hell-o Iam hear,take me away.","IF YOU LIKE A LAUGH IM YOU MAN THANX","Looking for a man with a Heart","Nice guy just looking to meet new people","Tall Dark & Handsome","come and say hi","im 6 180 brown brown luv sex makeing girls feel good","nice guy looking for a loving relationship with no games"," looking good friend"} !
!
| -0.0150456
clubs | my_desc | 0 | 230 | 29320 | {""," "} | {0.103,0.001} | {"
<BR>
<BR> english speaking man searching for partner in norway near oslo
<BR>
<BR>
<BR> are single and miss you
<BR>
<BR>
<BR> lets share the lonely nights ","Drop me a line . U will not regret coz u just meet a chance of a lifetime ...Still thinking ?still wondering ? stop ! Write to me now and i'll get back to u !","I am 35. I love good sex. I enjoy candle light, showers, baths, oil massages, give and recieve. I don't need intercourse to be satisfied. I think you need to use your imagination if you don't.","I am intelligent,well-read,kind, emotional, have a good sense of humor. I dislike egoism,pettiness and dishonesty.","I'm 23 in Notting Hill, looking for a woman older or younger for an int!
imate secret encounter. I'm 5'11, dark hair, blue eyes, slim build. I regularly work out in the gym, and am ready to have a work out in you.","Im a hard working man who would like a very
<BR>sexy attractive women to share good times
<BR>with here in South Florida. Walks by the
<BR>Ocean, fine dining and just hanging out
<BR>together would be nice.","Not into ego trips or head games. I'm in a comfortable place in my life where I don't have to prove myself personally or professionally. I just desire to enjoy life and its many different venues.","blue eye red head - thus the name foxxy ... Looking for you to share whatever your hearts desire might be ...","i am single libyan man , friendship means more to me , so i long to do good and wide friendship , i wish all over the world , so if you want to talk with me this is my email
<BR>abdul_zr(at)yahoo(dot)com","looking for fun and cassual sex with serious ladies 'your pleasure is mine also'if you want to get crazy and have real fun in a!
safe manner call me ",nmcjdkf} | -0.0011142
clubs | ur_desc | 0 | 4 | 1 | {""} | {1} | !
!
| 1
clubs | pictures | 0 | 26 | 2 | {"{0,0,0}","{1,0,0}"} | {0.889,0.111} | !
!
|
clubs | voice | 0 | 2 | 2 | {0,1} | {0.999333,0.000666667} | !
!
| 0.998169
(11 rows)

relname | reltype | relowner | relam | relfilenode | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
---------+---------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
clubs | 5535242 | 1003 | 0 | 5535241 | 25552 | 486011 | 5535243 | 0 | t | f | r | 11 | 0 | 0 | 0 | 0 | 0 | t | f | f | f |
(1 row)

And just in case it has relevance:

iwantu=# \d clubs_idx
Index "clubs_idx"
Column | Type
--------+---------
uid | bigint
club | integer
btree