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

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Cc: Ivan Voras <ivoras@freebsd.org>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: ORDER BY, LIMIT and indexes
Date: Wed, 07 Aug 2013 00:52:58 -0400
Message-ID: <3619.1375851178@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGTBQpbqNOfO2tfnxanw-Stv8FTwkKa_K1uQd9Y2ZEJvRi8YwA@mail.gmail.com>
References: <CAF-QHFW2S7XHMFY-nCSbBRsFQFC--3NyVqOcW7SMeNAs28NDcQ@mail.gmail.com>
	<CAGTBQpaUJz7jWpOdrOLiVteWvbYe8O4n37dy9k4hc2Dx1r5Ceg@mail.gmail.com>
	<CAB7nPqRi=9EUa3PEOoaUggh8MHpHBSEjrghGFjdpH=NQKfEBFQ@mail.gmail.com>
	<CAF-QHFVQ1qBh=oMwQ7ohYnza6GDV0OUz_=SQ0hd1A_agceZFQA@mail.gmail.com>
	<CAF-QHFUQaXW8E-n2okPJ8vbsKWCQZDYYYkK-37Du1YkZhc4Zkg@mail.gmail.com>
	<52017F1F.7020700@catalyst.net.nz>
	<CAGTBQpYnm9WYQ=3F2VhL1ZsTXeQQg2ujQbxeMnSgeLhkpV67SQ@mail.gmail.com>
	<CAGTBQpbqNOfO2tfnxanw-Stv8FTwkKa_K1uQd9Y2ZEJvRi8YwA@mail.gmail.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

Claudio Freire <klaussfreire@gmail.com> writes:
> Alternatively, a token startup cost could be added to those kinds of
> filtered sequential scans, when the filtering term is selective
> enough. That would offset the cost just a little bit, but enough to
> favor index over sequential on the right cases.

Maybe not so "token".  Really, if there's a filter condition having a
selectivity of say 1/N, we should expect to have to skip over O(N) tuples
before finding a match.  (Not sure at this late hour if the expectation is
N, or N/2, or something else ... but anyway it's in that ballpark.)  We
don't take that into account in computing the startup time of a plan node,
but I'm thinking we should.  In this particular example, that would
penalize the seqscan plan and not the indexscan plan, because in the
indexscan case the condition is being applied by the index; it's not an
after-the-fact filter.

			regards, tom lane


-- 
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 (17+ messages)

Message-ID: <3619.1375851178@sss.pgh.pa.us>
Permalink:  ../3619.1375851178@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3619.1375851178@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, klaussfreire@gmail.com, mark.kirkwood@catalyst.net.nz, ivoras@freebsd.org, michael.paquier@gmail.com
  Subject: Re: ORDER BY, LIMIT and indexes
  In-Reply-To: <3619.1375851178@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