To: "Joshua Marsh" <icub3d@gmail.com>
cc: "Terje Elde" <terje@elde.net>, "Jeff Davis" <pgsql@j-davis.com>,
	pgsql-performance@postgresql.org, jim@nasby.net
Subject: Re: Performance With Joins on Large Tables 
In-reply-to: <38242de90609131445p5044fa67p3752e1bc7f699da2@mail.gmail.com> 
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>
	<38242de90609131445p5044fa67p3752e1bc7f699da2@mail.gmail.com>
Comments: In-reply-to "Joshua Marsh" <icub3d@gmail.com>
	message dated "Wed, 13 Sep 2006 15:45:12 -0600"
Date: Wed, 13 Sep 2006 18:08:42 -0400
Message-ID: <18008.1158185322@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Joshua Marsh" <icub3d@gmail.com> writes:
>>> On 9/13/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> 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)

Wow, that correlation value is *way* away from order.  If they were
really in exact order by dsiacctno then I'd expect to see 1.0 in
that column.  Can you take another look at the tables and confirm
the ordering?  Does the correlation change if you do an ANALYZE on the
tables?  (Some small change is to be expected due to random sampling,
but this is way off.)

			regards, tom lane