Re: Performance With Joins on Large Tables

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.