pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Jake Nielsen <jake.k.nielsen@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Unexpected expensive index scan
Date: Fri, 30 Sep 2016 17:19:59 -0500
Message-ID: <7abde325-3452-a1e1-e288-ee33408d7708@BlueTreble.com> (raw)
In-Reply-To: <CAP3LSG4Ke7Kb1iZWhOWXczPT0kYWdriPFHAdGcLXkv_LMb4GJA@mail.gmail.com>
References: <CAP3LSG7iwYS6V555qqjNBvuoi3gsMhTU-nN7PcfxJhHhJVbXRw@mail.gmail.com>
	<CAP3LSG7PfEfaK5V6rT7rgP1MjLED-wnqGY5Qtp5Gx0s64L16tg@mail.gmail.com>
	<00e901d21921$1e647180$5b2d5480$@runbox.com>
	<CAP3LSG6UgeTWGY7_NQr6b1WRVdAnKdWqa0S=1mOuVq_hWK5OHw@mail.gmail.com>
	<CAP3LSG5bH-r=bO8YWLVziuNT37NmjFMV3qdhE543i+ruPxuUwQ@mail.gmail.com>
	<CAP3LSG5ejP9Ezm_wOcVV5C=Fs5hHHctF6xNGS1=fqLjEjU7yYA@mail.gmail.com>
	<15575.1475067892@sss.pgh.pa.us>
	<CAP3LSG4Ke7Kb1iZWhOWXczPT0kYWdriPFHAdGcLXkv_LMb4GJA@mail.gmail.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

On 9/28/16 1:11 PM, Jake Nielsen wrote:
> Beautiful! After changing the random_page_cost to 1.0 the original query
> went from ~3.5s to ~35ms. This is exactly the kind of insight I was
> fishing for in the original post. I'll keep in mind that the query
> planner is very tunable and has these sorts of hardware-related
> trade-offs in the future. I can't thank you enough!

Be careful with setting random_page_cost to exactly 1... that tells the 
planner that an index scan has nearly the same cost as a sequential 
scan, which is absolutely never the case, even with the database in 
memory. 1.1 or maybe even 1.01 is probably a safer bet.

Also note that you can set those parameters within a single session, as 
well as within a single transaction. So if you need to force a different 
setting for a single query, you could always do

BEGIN;
SET LOCAL random_page_cost = 1;
SELECT ...
COMMIT; (or rollback...)
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


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



view thread (9+ messages)

Message-ID: <7abde325-3452-a1e1-e288-ee33408d7708@BlueTreble.com>
Permalink:  ../7abde325-3452-a1e1-e288-ee33408d7708@BlueTreble.com/
Also on:    postgresql.org/message-id/7abde325-3452-a1e1-e288-ee33408d7708@BlueTreble.com

 · 

reply
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: Jim.Nasby@BlueTreble.com, jake.k.nielsen@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: Unexpected expensive index scan
  In-Reply-To: <7abde325-3452-a1e1-e288-ee33408d7708@BlueTreble.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