From: Tim Jones <TJones@optio.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: strange query behavior
Date: Thu, 14 Dec 2006 09:02:08 -0500
Message-ID: <47668A1334CDBF46927C1A0DFEB223D39F7B01@mail.optiosoftware.com> (raw)
In-Reply-To: <12988.1166052295@sss.pgh.pa.us>
References: <47668A1334CDBF46927C1A0DFEB223D39F79A5@mail.optiosoftware.com>
<10648.1166037392@sss.pgh.pa.us>
<47668A1334CDBF46927C1A0DFEB223D39F79FA@mail.optiosoftware.com>
<12988.1166052295@sss.pgh.pa.us>
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@sss.pgh.pa.us]
Sent: Wednesday, December 13, 2006 6:25 PM
To: Tim Jones
Cc: pgsql-performance@postgresql.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: TJones@optio.com, tgl@sss.pgh.pa.us
Subject: Re: strange query behavior
In-Reply-To: <47668A1334CDBF46927C1A0DFEB223D39F7B01@mail.optiosoftware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox