Date: Mon, 26 Jun 2017 10:01:20 -0700
From: Karl Czajkowski <karlcz@isi.edu>
To: Chris Wilson <chris+postgresql@qwirx.com>
Cc: pgsql-performance@postgresql.org, george.saklatvala@cantabcapital.com
Subject: Re: Fwd: Slow query from ~7M rows, joined to two tables of ~100 rows
 each
Message-ID: <20170626170120.GB27236@moraine.isi.edu>
References: 
 <CAOg7f817QD6XpSJD86pVJNLjmE_PtXUGpHOHk7BEr5ELBqV_7g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAOg7f817QD6XpSJD86pVJNLjmE_PtXUGpHOHk7BEr5ELBqV_7g@mail.gmail.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Jun 26, Chris Wilson modulated:
> ...
>     In your case, the equivalent hack would be to compile the small
>     dimension tables into big CASE statements I suppose...
> 
> 
> Nice idea! I tried this but unfortunately it made the query 16 seconds
> slower (up to 22 seconds) instead of faster.

Other possible rewrites to try instead of joins:

  -- replace the case statement with a scalar subquery

  -- replace the case statement with a stored procedure wrapping that scalar subquery
     and declare the procedure as STABLE or even IMMUTABLE

These are shots in the dark, but seem easy enough to experiment with and might
behave differently if the query planner realizes it can cache results for 
repeated use of the same ~100 input values.


Karl


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance