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: Thu, 14 Sep 2006 08:18:30 -0600
Message-ID: <38242de90609140718w2d10cfdfueb36bb53285c6cfa@mail.gmail.com> (raw)
In-Reply-To: <18008.1158185322@sss.pgh.pa.us>
References: <38242de90609121517y62517040v8622b9ae93cba23d@mail.gmail.com>
<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>
<18008.1158185322@sss.pgh.pa.us>
>
> 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
Thanks for pointing that out. Generally we load the tables via COPY and
then never touch the data. Because of the slowdown, I have been updating
tuples. I reloaded it from scratch, set enable_seqscan=off and
random_access_age=4 and I got the results I was looking for:
data=# analyze view_505;
ANALYZE
data=# analyze r3s169;
ANALYZE
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 | 1
r3s169 | dsiacctno | 42140 | 13 | 1
(2 rows)
data=# explain SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
v.custdate), EXTRACT (EPOCH FROM s.custdate) FROM view_505 v INNER JOIN
r3s169 s on v.dsiacctno = s.dsiacctno;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Merge Join (cost=0.00..20099712.79 rows=285153952 width=11)
Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
-> Index Scan using view_505_dsiacctno on view_505 v (cost=
0.00..5147252.74 rows=112282976 width=20)
-> Index Scan using r3s169_dsiacctno on r3s169 s
(cost=0.00..8256331.47rows=285153952 width=17)
(4 rows)
Thanks for you help everyone.
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: <38242de90609140718w2d10cfdfueb36bb53285c6cfa@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