Date: Mon, 26 Jun 2017 13:32:21 -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: <20170626203221.GA10516@moraine.isi.edu>
References: 
 <CAOg7f817QD6XpSJD86pVJNLjmE_PtXUGpHOHk7BEr5ELBqV_7g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAOg7f817QD6XpSJD86pVJNLjmE_PtXUGpHOHk7BEr5ELBqV_7g@mail.gmail.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Jun 26, Chris Wilson modulated:

> I created the index starting with date and it did make a big
> difference: down to 10.3 seconds using a bitmap index scan and bitmap
> heap scan (and then two hash joins as before).
> 

By the way, what kind of machine are you using?  CPU, RAM, backing
storage?

I tried running your original test code and the query completed in
about 8 seconds, and adding the index changes and analyze statement
brought it down to around 2.3 seconds on my workstation with Postgres
9.5.7.  On an unrelated development VM with Postgres 9.6.3, the final
form took around 4 seconds.


Karl


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