From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Gopisetty, Ramesh <rameshg2@illinois.edu>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Performance Issue (Not using Index when joining two tables).
Date: Sun, 13 Sep 2020 13:07:07 -0400
Message-ID: <850901.1600016827@sss.pgh.pa.us> (raw)
In-Reply-To: <20200913164745.dfnoeiz54wgmgckn@development>
References: <BL0PR11MB3153E3C88990EB0A7976688CEE220@BL0PR11MB3153.namprd11.prod.outlook.com>
<20200913164745.dfnoeiz54wgmgckn@development>
Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
> Most of the time (3460ms) is spent in the sequential scan on
> chr_simple_val, and the seqscan on chr_emp_position is taking ~330ms).
> Combined that's 3790ms out of 3797ms, so the join is pretty much
> irrelevant.
> Either the seqscans are causing a lot of I/O, or maybe the f_sel_*
> functions in the filter are expensive. Judging by how few rows are in
> the tables (not sure how large the tables are), I'd guess it's the
> latter ... Hard to say without knowing what the functions do etc.
I think the OP is wishing that the filter functions for the larger table
would be postponed till after the join condition is applied. I'm a
little dubious that that's going to save anything meaningful; but maybe
increasing the cost attributed to those functions would persuade the
planner to try it that way.
First though, does forcing a nestloop plan (turn off enable_hashjoin,
and enable_mergejoin too if needed) produce the shape of plan you
want? And if so, is it actually faster? Only if those things are
true is it going to be worth messing with costing parameters.
regards, tom lane
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, tomas.vondra@2ndquadrant.com, rameshg2@illinois.edu, pgsql-performance@lists.postgresql.org
Subject: Re: Performance Issue (Not using Index when joining two tables).
In-Reply-To: <850901.1600016827@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