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

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [PERFORM] Slow query: bitmap scan troubles
Date: Mon, 14 Jan 2013 12:23:17 -0500
Message-ID: <23869.1358184197@sss.pgh.pa.us> (raw)
In-Reply-To: <CA+TgmoYQ6Nq-tpHiDPCUH3CkH2N9D67=oDKJtLxuRRC=dRteSQ@mail.gmail.com>
References: <092a01cdd230$ff6143c0$fe23cb40$@foo.me.uk>
	<CAGTBQpaKOfZs+qy+S7CCPcTg7PYOrwgcQAXgWV88cQTn-BFstg@mail.gmail.com>
	<CAMkU=1wEc1cCeUdR1oZ3Q2fe2pGj6tvrwRcY2obbfEk8LcNypQ@mail.gmail.com>
	<097601cdd249$ae338530$0a9a8f90$@foo.me.uk>
	<CAMkU=1wmxKifgUufbSffQ+VH3kFNczK65MxXetwVFJZd3zvkeA@mail.gmail.com>
	<CAMkU=1w40aQt0twukCsvL2hPHUPujLqJBe8v90phuqBJ5wgw1Q@mail.gmail.com>
	<9022.1354730710@sss.pgh.pa.us>
	<CAMkU=1ykyMgXccv2dAWKOXdDx5U0Qpc28aW6k659hvE7VZEMXQ@mail.gmail.com>
	<28944.1357424296@sss.pgh.pa.us>
	<CA+U5nMLJBrnU+B6md5VR1GHGz6Ji5MEZd6QE+favQYLjv9H8JA@mail.gmail.com>
	<18164.1357498720@sss.pgh.pa.us>
	<CA+U5nMLjf2-kTa4-AR-0XLKKwbc+=_fb4237i_UAWYzowW+-1Q@mail.gmail.com>
	<8539.1357513385@sss.pgh.pa.us>
	<12791.1357580151@sss.pgh.pa.us>
	<CA+U5nMKbOGVfQXfJi5_vOUPEatF_V_+e_HX4P5R=tb9JSo2ceA@mail.gmail.com>
	<13842.1357583258@sss.pgh.pa.us>
	<13967.1357866454@sss.pgh.pa.us>
	<CA+TgmoYQ6Nq-tpHiDPCUH3CkH2N9D67=oDKJtLxuRRC=dRteSQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>

Robert Haas <robertmhaas@gmail.com> writes:
> I'm not sure I have anything intelligent to add to this conversation -
> does that make me the wisest of all the Greeks? - but I do think it
> worth mentioning that I have heard occasional reports within EDB of
> the query planner refusing to use extremely large indexes no matter
> how large a hammer was applied.  I have never been able to obtain
> enough details to understand the parameters of the problem, let alone
> reproduce it, but I thought it might be worth mentioning anyway in
> case it's both real and related to the case at hand.  Basically I
> guess that boils down to: it would be good to consider whether the
> costing model is correct for an index of, say, 1TB.

Well, see the cost curves at
http://www.postgresql.org/message-id/13967.1357866454@sss.pgh.pa.us

The old code definitely had an unreasonably large charge for indexes
exceeding 1e8 or so tuples.  This wouldn't matter that much for simple
single-table lookup queries, but I could easily see it putting the
kibosh on uses of an index on the inside of a nestloop.

It's possible that the new code goes too far in the other direction:
we're now effectively assuming that all inner btree pages stay in cache
no matter how large the index is.  At some point it'd likely be
appropriate to start throwing in some random_page_cost charges for inner
pages beyond the third/fourth/fifth(?) level, as Simon speculated about
upthread.  But I thought we could let that go until we start seeing
complaints traceable to it.

			regards, tom lane


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



view thread (54+ messages)  latest in thread

Message-ID: <23869.1358184197@sss.pgh.pa.us>
Permalink:  ../23869.1358184197@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/23869.1358184197@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, robertmhaas@gmail.com, simon@2ndquadrant.com, jeff.janes@gmail.com, pgsql-hackers@postgresql.org
  Subject: Re: [PERFORM] Slow query: bitmap scan troubles
  In-Reply-To: <23869.1358184197@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