Skip site navigation (1) Skip section navigation (2)

Re: Performance With Joins on Large Tables

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Joshua Marsh" <icub3d(at)gmail(dot)com>
Cc: "Terje Elde" <terje(at)elde(dot)net>, "Jeff Davis" <pgsql(at)j-davis(dot)com>, pgsql-performance(at)postgresql(dot)org, jim(at)nasby(dot)net
Subject: Re: Performance With Joins on Large Tables
Date: 2006-09-13 22:08:42
Message-ID: 18008.1158185322@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Joshua Marsh" <icub3d(at)gmail(dot)com> writes:
>>> On 9/13/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Merlin MoncureDate: 2006-09-13 23:42:21
Subject: Re: sql-bench
Previous:From: Joshua MarshDate: 2006-09-13 21:45:12
Subject: Re: Performance With Joins on Large Tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group