Re: bug in join?

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"}
| | 1
b | 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.454545
b | input_tag | 0 | 16 | 1 | {"AirLinkInput"} | {"1"} | | 1
b | 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.454545
b | b_distance | 0 | 4 | 1 | {"200"} | {"1"} | | 1
b | 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.912263
d | 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.451197
d | d_passwd | 0 | 9 | 1 | {"Czech"} | {"1"} | | 1
d | d_port | 0 | 4 | 1 | {"22335"} | {"1"} |
| 1
d | d_type | 0 | 9 | 2 | {"signs","buses"} | {"0.740741","0.259259"} | | 0.912263
d | d_status | 0 | 4 | 1 | {"0"} | {"1"} | | 1
(6 rows)

Thanks!

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

> Laurette Cisneros <laurette(at)nextbus(dot)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