Re: Optimizer(?) off by factor of 3 ... ? - Mailing list pgsql-hackers
| From | Marc G. Fournier |
|---|---|
| Subject | Re: Optimizer(?) off by factor of 3 ... ? |
| Date | |
| Msg-id | 20020211130607.X59276-100000@mail1.hub.org Whole thread Raw |
| In response to | Re: Optimizer(?) off by factor of 3 ... ? (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: Optimizer(?) off by factor of 3 ... ?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-hackers |
On Mon, 11 Feb 2002, Tom Lane wrote:
> "Marc G. Fournier" <scrappy@hub.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=23510loops=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,"Lookingfor 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
getsum","Hell-o Iam hear,take me away.","IF YOU LIKE A LAUGH IM YOU MAN THANX","Looking for a man with a Heart","Nice
guyjust looking to meet new people","Tall Dark & Handsome","come and say hi","im 6 180 brown brown luv sex makeing
girlsfeel 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
regretcoz u just meet a chance of a lifetime ...Still thinking ?still wondering ? stop ! Write to me now and i'll get
backto u !","I am 35. I love good sex. I enjoy candle light, showers, baths, oil massages, give and recieve. I don't
needintercourse 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
inNotting 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
havea work out in you.","Im a hard working man who would like a very
<BR>sexy attractive women to share good
times
<BR>withhere in South Florida. Walks by the
<BR>Ocean, fine dining and just hanging out
<BR>together would be
nice.","Notinto ego trips or head games. I'm in a comfortable place in my life where I don't have to prove myself
personallyor professionally. I just desire to enjoy life and its many different venues.","blue eye red head - thus the
namefoxxy ... Looking for you to share whatever your hearts desire might be ...","i am single libyan man , friendship
meansmore 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
thisis my email
<BR>abdul_zr@yahoo.com","looking for fun and cassual sex with serious ladies 'your pleasure is mine
also'ifyou want to get crazy and have real fun in a!
safe manner call me
",ÐԸеط½¿ÉÊǼè¿àµ«ÊÇÃDzÅСÁË¿Éû£¬³¤£¬¿É·¢µÄÃÀÀö£¬¿´¼û²Ð¿á·ßÅû£¬´óÅ®³§²Å£¬ÏÂÃæÄÔ½îÀ©´ó·¨¡£Àñò£¬·À¡£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
pgsql-hackers by date: