From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <Kgrittn.CCAP.Courts@wicourts.gov>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: hashjoin chosen over 1000x faster plan
Date: Wed, 10 Oct 2007 16:02:59 -0500
Message-ID: <470CF7B3.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <470CF450.EE98.0025.0@wicourts.gov>
References: <470B99BE.EE98.0025.0@wicourts.gov>
<1191997904.4233.125.camel@ebony.site>
<470C9813.EE98.0025.0@wicourts.gov>
<1192035955.4233.306.camel@ebony.site>
<1192035955.4233.306.camel@ebony.site>
<20980.1192039650@sss.pgh.pa.us>
<470CD3E3.EE98.0025.0@wicourts.gov>
<1192042492.4233.334.camel@ebony.site>
<470CE34D.EE98.0025.0@wicourts.gov>
<470CE34D.EE98.0025.0@wicourts.gov>
<23650.1192048377@sss.pgh.pa.us>
<470CF450.EE98.0025.0@wicourts.gov>
>>> On Wed, Oct 10, 2007 at 3:48 PM, in message
<470CF450.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:
>
> This ten times faster
That understates it -- I forgot to get things cached, as I had done
for all the other tests. When cached, this is sub-millisecond,
although not quite the 1000-fold increase which I get when no matches
are found.
-Kevin
Sort (cost=126.70..126.70 rows=1 width=168) (actual time=0.259..0.261 rows=2 loops=1)
Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo"
-> Nested Loop Left Join (cost=0.00..126.69 rows=1 width=168) (actual time=0.157..0.234 rows=2 loops=1)
Join Filter: ((d."countyNo")::smallint = (c."countyNo")::smallint)
-> Nested Loop (cost=0.00..123.44 rows=1 width=185) (actual time=0.139..0.203 rows=2 loops=1)
-> Index Scan using "ControlRecord_pkey" on "ControlRecord" c (cost=0.00..4.27 rows=1 width=2) (actual time=0.024..0.026 rows=1 loops=1)
Index Cond: (13 = ("countyNo")::smallint)
-> Nested Loop (cost=0.00..119.17 rows=1 width=185) (actual time=0.109..0.169 rows=2 loops=1)
-> Nested Loop (cost=0.00..115.67 rows=1 width=131) (actual time=0.087..0.127 rows=2 loops=1)
-> Index Scan using "Charge_pkey" on "Charge" "CH" (cost=0.00..10.69 rows=4 width=112) (actual time=0.038..0.051 rows=4 loops=1)
Index Cond: ((("countyNo")::smallint = 13) AND (("caseNo")::bpchar = '2004CF002575'::bpchar))
-> Index Scan using "CaseHist_pkey" on "CaseHist" "CHST" (cost=0.00..26.18 rows=5 width=41) (actual time=0.014..0.015 rows=0 loops=4)
Index Cond: ((13 = ("CHST"."countyNo")::smallint) AND ('2004CF002575'::bpchar = ("CHST"."caseNo")::bpchar) AND (("CHST"."histSeqNo")::smallint = ("CH"."reopHistSeqNo")::smallint))
-> Index Scan using "CaseTypeHistEventB_pkey" on "CaseTypeHistEventB" b (cost=0.00..3.48 rows=1 width=69) (actual time=0.015..0.017 rows=1 loops=2)
Index Cond: ((("CHST"."caseType")::bpchar = (b."caseType")::bpchar) AND (("CHST"."eventType")::bpchar = (b."eventType")::bpchar))
-> Index Scan using "CaseTypeHistEventD_CaseType" on "CaseTypeHistEventD" d (cost=0.00..3.23 rows=1 width=17) (actual time=0.011..0.011 rows=0 loops=2)
Index Cond: (((d."caseType")::bpchar = (b."caseType")::bpchar) AND ((d."eventType")::bpchar = (b."eventType")::bpchar))
Total runtime: 0.605 ms
(18 rows)
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: Kevin.Grittner@wicourts.gov, tgl@sss.pgh.pa.us, Kgrittn.CCAP.Courts@wicourts.gov, simon@2ndquadrant.com
Subject: Re: hashjoin chosen over 1000x faster plan
In-Reply-To: <470CF7B3.EE98.0025.0@wicourts.gov>
* 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