Re: strange query behavior

18,273,008 rows in observationresults

pg_stats:

select * from pg_stats where tablename='observationresults' and
attname='batteryidentifier';

schemaname | tablename | attname | null_frac |
avg_width | n_distinct | most_common_vals
| most_common_freqs
| histogram_bounds
| correlation
------------+--------------------+-------------------+-----------+------
-----+------------+-----------------------------------------------------
---------------------+--------------------------------------------------
-----------------------+------------------------------------------------
-------------------------------------+-------------
public | observationresults | batteryidentifier | 0 |
4 | 12942 |
{437255,1588952,120420,293685,356599,504069,589910,693683,834990,854693}
|
{0.00133333,0.00133333,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001}
|
{3561,271263,556929,839038,1125682,1406538,1697589,1970463,2226781,25392
41,2810844} | 0.31779

thanks

Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, December 13, 2006 6:25 PM
To: Tim Jones
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] strange query behavior

The large rowcount estimate makes it back off to a non-nestloop plan for
the outer joins, and in this situation that's a loser.

I'm actually not sure why they're not both too high --- with the
rowcount estimate of 1362 for the inner scan in the first example, you'd
expect about twice that for the join result. But the immediate problem
is that in the case where it knows exactly what batteryidentifier is
being probed for, it's still off by more than a factor of 100 on the
rowcount estimate for observationresults. How many rows in
observationresults, and may we see the pg_stats entry for
observationresults.batteryidentifier?

It's likely that the answer for you will be "raise the statistics target
for observationresults and re-ANALYZE", but I'd like to gather more info
about what's going wrong first.

regards, tom lane