From: Joshua Marsh <icub3d@gmail.com>
To: Jeff Davis <pgsql@j-davis.com>
To: pgsql-performance@postgresql.org
To: jim@nasby.net
Subject: Re: Performance With Joins on Large Tables
Date: Wed, 13 Sep 2006 13:27:06 -0600
Message-ID: <38242de90609131227q4837d1cekc50bc1a450175bec@mail.gmail.com> (raw)
In-Reply-To: <1158167054.29889.72.camel@dogma.v10.wvs>
References: <38242de90609121517y62517040v8622b9ae93cba23d@mail.gmail.com>
<20060913064152.GC23173@nasby.net>
<38242de90609130749u976295fwb10904ff4797b920@mail.gmail.com>
<1158163655.29889.57.camel@dogma.v10.wvs>
<38242de90609130919k33208204ka1006ff0ca25f75b@mail.gmail.com>
<1158167054.29889.72.camel@dogma.v10.wvs>
> Hmm... that sounds bad. I'm sure your system will always choose indexes
> with that value.
>
> Is it overestimating the cost of using indexes or underestimating the
> cost of a seq scan, or both? Maybe explain with the 0.1 setting will
> help?
>
> Regards,
> Jeff Davis
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..51808909.26 rows=285230272 width=11)
Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
-> Index Scan using view_505_dsiacctno on view_505 v
(cost=0.00..12755411.69 rows=112393848 width=20)
-> Index Scan using r3s169_dsiacctno on r3s169 s
(cost=0.00..32357747.90 rows=285230272 width=17)
(4 rows)
This is what I wanted, two index scans. Just to give you an idea of
the difference in time, this plan would allow me to process 100,000
records ever few seconds, while the sequential scan would only
produces 100,000 every 10 minutes.
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, pgsql@j-davis.com, jim@nasby.net
Subject: Re: Performance With Joins on Large Tables
In-Reply-To: <38242de90609131227q4837d1cekc50bc1a450175bec@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