Skip site navigation (1) Skip section navigation (2)

Re: bug in join?

From: Laurette Cisneros <laurette(at)nextbus(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: bug in join?
Date: 2002-01-02 23:32:43
Message-ID: Pine.LNX.4.33.0201021521210.18529-100000@visor.corp.nextbus.com (view raw or flat)
Thread:
Lists: 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"}
          |                  |           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


In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2002-01-02 23:40:20
Subject: Re: Bulkloading using COPY - ignore duplicates?
Previous:From: Bruce MomjianDate: 2002-01-02 23:17:42
Subject: Re: software license question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group