pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joshua Marsh <icub3d@gmail.com>
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 18:08:42 -0400
Message-ID: <18008.1158185322@sss.pgh.pa.us> (raw)
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>

"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



view thread (16+ messages)  latest in thread

Message-ID: <18008.1158185322@sss.pgh.pa.us>
Permalink:  ../18008.1158185322@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/18008.1158185322@sss.pgh.pa.us

 · 

reply
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: tgl@sss.pgh.pa.us, icub3d@gmail.com, terje@elde.net, pgsql@j-davis.com, jim@nasby.net
  Subject: Re: Performance With Joins on Large Tables
  In-Reply-To: <18008.1158185322@sss.pgh.pa.us>

* 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