From: Joshua Marsh <icub3d@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Terje Elde <terje@elde.net>
Cc: Jeff Davis <pgsql@j-davis.com>
Cc: pgsql-performance@postgresql.org
Cc: jim@nasby.net
Subject: Re: Performance With Joins on Large Tables
Date: Wed, 13 Sep 2006 15:45:12 -0600
Message-ID: <38242de90609131445p5044fa67p3752e1bc7f699da2@mail.gmail.com> (raw)
In-Reply-To: <16941.1158182624@sss.pgh.pa.us>
References: <38242de90609121517y62517040v8622b9ae93cba23d@mail.gmail.com>
<38242de90609130749u976295fwb10904ff4797b920@mail.gmail.com>
<1158163655.29889.57.camel@dogma.v10.wvs>
<38242de90609130919k33208204ka1006ff0ca25f75b@mail.gmail.com>
<1158167054.29889.72.camel@dogma.v10.wvs>
<45085F3A.1040108@elde.net>
<38242de90609131327o68d2866fh92c12ddcf364aba7@mail.gmail.com>
<16645.1158181784@sss.pgh.pa.us>
<38242de90609131418j43d0dc88q3cb8a52dcceec287@mail.gmail.com>
<16941.1158182624@sss.pgh.pa.us>
On 9/13/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Joshua Marsh" <icub3d@gmail.com> writes:
> >> Are the tables perhaps nearly in order by the dsiacctno fields?
>
> > My assumption would be they are in exact order. The text file I used
> > in the COPY statement had them in order, so if COPY preserves that in
> > the database, then it is in order.
>
> Ah. So the question is why the planner isn't noticing that. What do
> you see in the pg_stats view for the two dsiacctno fields --- the
> correlation field in particular?
Here are the results:
data=# select tablename, attname, n_distinct, avg_width, correlation
from pg_stats where tablename in ('view_505', 'r3s169') and attname =
'dsiacctno';
tablename | attname | n_distinct | avg_width | correlation
-----------+-----------+------------+-----------+-------------
view_505 | dsiacctno | -1 | 13 | -0.13912
r3s169 | dsiacctno | 44156 | 13 | -0.126824
(2 rows)
Someone suggested CLUSTER to make sure they are in fact ordered, I can
try that to and let everyone know the results.
> > The system has 8GB of ram and work_mem is set to 256MB.
>
> Seems reasonable enough. BTW, I don't think you've mentioned exactly
> which PG version you're using?
>
> regards, tom lane
>
I am using 8.0.3.
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: icub3d@gmail.com, tgl@sss.pgh.pa.us, terje@elde.net, pgsql@j-davis.com, jim@nasby.net
Subject: Re: Performance With Joins on Large Tables
In-Reply-To: <38242de90609131445p5044fa67p3752e1bc7f699da2@mail.gmail.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