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: 47668A1334CDBF46927C1A0DFEB223D39F7B01@mail.optiosoftware.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

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-2014 The PostgreSQL Global Development Group