Re: bug in join? - Mailing list pgsql-hackers

OK, I reproduced it again.  I had restored the data for the tables from the
wrong backup.  I restored the two tables from last night's backup (BTW, we use
pg_dump -a -O -Fc...) and ran vacuum analyze and it reproduces (each and
every time).

We've turned off vacuum analyze (we do it every night *after* the backup)
for now.  We would love for this to get fixed asap (of course ;.)

Here's the info. you asked for:

select * from pg_stats where tablesname ='b':
tablename |   attname    | null_frac | avg_width | n_distinct | most_common_vals |
most_common_freqs| histogram_bounds | correlation
 

-----------+--------------+-----------+-----------+------------+---------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+------------------+-------------b
       | rev          |         0 |         4 |  -0.142857 | {"0","1001","1002"} | {"0.333333","0.333333","0.333333"}
     |                  |           1b         | b_tag        |         0 |         7 |  -0.333333 |
{"S001","S002","S003","S004","S005","VT1","VT2"}|
{"0.142857","0.142857","0.142857","0.142857","0.142857","0.142857","0.142857"}|                  |    0.454545b
|input_tag    |         0 |        16 |          1 | {"AirLinkInput"} | {"1"} |                  |           1b
|address      |         0 |        19 |  -0.333333 |
{"166.128.052.237","166.128.053.084","166.128.054.017","166.128.054.018","166.128.057.250","166.128.058.202","166.128.058.203"}
|{"0.142857","0.142857","0.142857","0.142857","0.142857","0.142857","0.142857"} |                  |    0.454545b
 | b_distance   |         0 |         4 |          1 | {"200"} | {"1"} |                  |           1b         |
b_time       |         0 |         4 |          1 | {"90"} | {"1"} |                  |           1
 
(6 rows)

select * from pg_status where tablename = 'd';tablename |   attname   | null_frac | avg_width | n_distinct |
most_common_vals|                               most_common_freqs | histogram_bounds | correlation
 

-----------+-------------+-----------+-----------+------------+---------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------d
       | rev         |         0 |         4 |          3 | {"0","1002","1001"} | {"0.45679","0.45679","0.0864198"} | |
  0.912263d         | address     |         0 |        19 |   -0.45679 |
{"166.128.052.237","166.128.053.084","166.128.054.017","166.128.054.018","166.128.057.250","166.128.058.202","166.128.058.203"}
|{"0.037037","0.037037","0.037037","0.037037","0.037037","0.037037","0.037037"} |
{"166.128.169.189","166.128.169.199","166.128.169.236","166.128.171.002","166.129.108.003","166.132.126.184","166.133.174.093","166.133.174.098","166.204.012.171","166.204.045.135","166.204.066.001"}
|   0.451197d         | d_passwd    |         0 |         9 |          1 | {"Czech"} | {"1"} | |           1d         |
d_port     |         0 |         4 |          1 | {"22335"} | {"1"} |                                  |           1d
     | d_type      |         0 |         9 |          2 | {"signs","buses"} | {"0.740741","0.259259"} | |    0.912263d
      | d_status    |         0 |         4 |          1 | {"0"} | {"1"} | |           1
 
(6 rows)

Thanks!

Laurette
On Wed, 2 Jan 2002, Tom Lane wrote:

> Laurette Cisneros <laurette@nextbus.com> writes:
> > This join worked last week and today it gets and error:
> > select * from b, d
> >  where b.address = d.address;
> > It now fails with the following error:
> > ERROR:  join_selectivity: bad value -0.121693
>
> Probably what has changed is the pg_statistic data (VACUUM ANALYZE
> results).  Please send the results of
>
> select * from pg_stats where tablename = 'b';
> select * from pg_stats where tablename = 'd';
>
>             regards, tom lane
>

-- 
Laurette Cisneros
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Passenger Information Everywhere