On Mon, May 25, 2009 at 11:22 AM, David Blewett da...@dawninglight.net wrote:
On Sun, May 24, 2009 at 2:42 PM, Tom Lane t...@sss.pgh.pa.us wrote:
It still feels like this schema design is obscuring correlations that
the planner needs to know about in order to make decent estimates.
I'm not
David,
My first thought would be to increase statistics dramatically on the
filtered columns in hopes of making PG realize there's a lot of rows
there; it's off by 8x. Correlations stats are an ongoing issue in
PostgreSQL.
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
--
Sent
On Fri, Jun 5, 2009 at 7:32 PM, Josh Berkus j...@agliodbs.com wrote:
My first thought would be to increase statistics dramatically on the
filtered columns in hopes of making PG realize there's a lot of rows there;
it's off by 8x. Correlations stats are an ongoing issue in PostgreSQL.
I
On Fri, Jun 5, 2009 at 8:29 PM, David Blewettda...@dawninglight.net wrote:
On Fri, Jun 5, 2009 at 7:32 PM, Josh Berkus j...@agliodbs.com wrote:
My first thought would be to increase statistics dramatically on the
filtered columns in hopes of making PG realize there's a lot of rows there;
it's
On Sun, May 24, 2009 at 2:42 PM, Tom Lane t...@sss.pgh.pa.us wrote:
It still feels like this schema design is obscuring correlations that
the planner needs to know about in order to make decent estimates.
I'm not sure how to make the planner aware of these correlations. Is there
something
David Blewett da...@dawninglight.net writes:
I took the time to load this data into an 8.4beta2 install, and the same
query runs in a much more reasonable timeframe (~3s as opposed to ~50s). I
set the statistics target to 500, and got this explain [1].
1. http://explain.depesz.com/s/pw
Hmm...
On Sat, May 9, 2009 at 11:52 AM, Tom Lane t...@sss.pgh.pa.us wrote:
David Blewett da...@dawninglight.net writes:
On Fri, May 8, 2009 at 10:00 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Thanks. Could I trouble you for one other data point --- about how many
rows are in each of these tables?
On Sat, May 9, 2009 at 11:52 AM, Tom Lane t...@sss.pgh.pa.us wrote:
As best I can tell, the selectivity numbers are about what they should
be --- for instance, using these stats I get a selectivity of 0.074
for the join clause fkr.submission_id = tr.submission_id. Over the
entire
On Fri, May 8, 2009 at 10:00 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Thanks. Could I trouble you for one other data point --- about how many
rows are in each of these tables?
Not a problem:
canvas_dateresponse 263819
canvas_foreignkeyresponse 646484
canvas_integerresponse 875375
David Blewett da...@dawninglight.net writes:
On Fri, May 8, 2009 at 10:00 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Thanks. Could I trouble you for one other data point --- about how many
rows are in each of these tables?
Not a problem:
As best I can tell, the selectivity numbers are about
David Blewett da...@dawninglight.net writes:
On Thu, May 7, 2009 at 6:44 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Look into pg_stats for the rows concerning the columns used in the
query's WHERE and JOIN/ON clauses.
Okay, here you go:
http://rafb.net/p/20y8Oh72.html
I got some time to poke
David Blewett da...@dawninglight.net writes:
Apparently there was a typo in the query that I didn't notice that
excluded that table's columns. Here is the new output including it:
http://pastesite.com/7017
Thanks. Could I trouble you for one other data point --- about how many
rows are in
I have a query [1] that Postgres is insisting on using a Nested Loop
for some reason when a Hash Join is much faster. It seems like the
estimates are way off. I've set default_statistics_target to 250, 500,
1000 and analyzed, but they never seem to improve. If I disable
nestloops, the query
On Thu, May 7, 2009 at 12:53 PM, David Blewett da...@dawninglight.net wrote:
1. http://dpaste.com/hold/41842/
2. http://explain.depesz.com/s/Wg
3. http://explain.depesz.com/s/1s
4. http://dpaste.com/hold/41846/
Forgot to mention that I'm using Postgres 8.3.6 on linux 2.6.24.
Shared buffers
David Blewett da...@dawninglight.net writes:
On Thu, May 7, 2009 at 12:53 PM, David Blewett da...@dawninglight.net wrote:
1. http://dpaste.com/hold/41842/
2. http://explain.depesz.com/s/Wg
3. http://explain.depesz.com/s/1s
4. http://dpaste.com/hold/41846/
Forgot to mention that I'm using
On Thu, May 7, 2009 at 4:31 PM, Tom Lane t...@sss.pgh.pa.us wrote:
as few as ten rows out, it'd likely switch to a different plan. So the
So the question to ask is why the rowcount estimates are so abysmally bad.
You mentioned having tried to increase the stats targets, but without
seeing the
David Blewett da...@dawninglight.net writes:
On Thu, May 7, 2009 at 4:31 PM, Tom Lane t...@sss.pgh.pa.us wrote:
as few as ten rows out, it'd likely switch to a different plan. So the
So the question to ask is why the rowcount estimates are so abysmally bad.
You mentioned having tried to
On Thu, May 7, 2009 at 6:44 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Look into pg_stats for the rows concerning the columns used in the
query's WHERE and JOIN/ON clauses.
Okay, here you go:
http://rafb.net/p/20y8Oh72.html
David
--
Sent via pgsql-performance mailing list
18 matches
Mail list logo