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

Re: strange query behavior

From: "Tim Jones" <TJones(at)optio(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: strange query behavior
Date: 2006-12-14 14:02:08
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
18,273,008 rows in observationresults


select * from pg_stats where tablename='observationresults' and

 schemaname |     tablename      |      attname      | null_frac |
avg_width | n_distinct |                             most_common_vals
|                            most_common_freqs
|                                  histogram_bounds
| correlation
 public     | observationresults | batteryidentifier |         0 |
4 |      12942 |
41,2810844} |     0.31779


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

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

In response to


pgsql-performance by date

Next:From: Greg SmithDate: 2006-12-14 15:00:14
Subject: Re: New to PostgreSQL, performance considerations
Previous:From: Arnaud LesauvageDate: 2006-12-14 14:01:34
Subject: Re: Slow update with simple query

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