From: Tom Lane <tgl@sss.pgh.pa.us>
To: 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: Sun, 06 Jan 2013 13:58:40 -0500
Message-ID: <18164.1357498720@sss.pgh.pa.us> (raw)
In-Reply-To: <CA+U5nMLJBrnU+B6md5VR1GHGz6Ji5MEZd6QE+favQYLjv9H8JA@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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Simon Riggs <simon@2ndQuadrant.com> writes:
> On 5 January 2013 22:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> No. The argument is that if we don't have some such correction, the
>> planner is liable to believe that different-sized indexes have *exactly
>> the same cost*, if a given query would fetch the same number of index
>> entries.
> The only difference between a large and a small index is the initial
> fetch, since the depth of the index may vary. After that the size of
> the index is irrelevant to the cost of the scan, since we're just
> scanning across the leaf blocks. (Other differences may exist but not
> related to size).
Right: except for the "fudge factor" under discussion, all the indexscan
costs that we model come from accessing index leaf pages and leaf
tuples. So to the extent that the fudge factor has any principled basis
at all, it's an estimate of index descent costs. And in that role I
believe that total index size needs to be taken into account.
> Perhaps the cost of the initial fetch is what you mean by a
> "correction"? In that case, why not use the index depth directly from
> the metapage, rather than play with size?
IIRC, one of my very first attempts to deal with this was to charge
random_page_cost per level of index descended. This was such a horrid
overestimate that it never went anywhere. I think that reflects that in
practical applications, the upper levels of the index tend to stay in
cache. We could ignore I/O on that assumption and still try to model
CPU costs of the descent, which is basically what Jeff is proposing.
My objection to his formula is mainly that it ignores physical index
size, which I think is important to include somehow for the reasons
I explained in my other message.
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
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, simon@2ndQuadrant.com, jeff.janes@gmail.com, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] Slow query: bitmap scan troubles
In-Reply-To: <18164.1357498720@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