From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Tom Coogan <nocera@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Inefficient filter order in query plan
Date: Thu, 27 Feb 2014 13:24:11 -0500
Message-ID: <20140227182411.GR2921@tamriel.snowman.net> (raw)
In-Reply-To: <1633.1393520657@sss.pgh.pa.us>
References: <CAN07wvLhJhWApx2uGiyeOeCMtXjLw8W84vNNxrhcNvLxKjhTdQ@mail.gmail.com>
<1633.1393520657@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Tom Coogan <nocera@gmail.com> writes:
> > I'd like to understand why PostgreSQL is choosing to filter on the most
> > inefficient predicate first in the query below.
>
> It doesn't know that LIKE is any more expensive than the other operators,
> so there's no reason to do them in any particular order.
>
> You could try increasing the cost attributed to the texticlike() function
> if you don't like the results you're getting here.
Perhaps we should be attributing some additional cost to operations
which (are likely to) require de-TOAST'ing a bunch of values? It's not
obvious from the original email, but it's at least my suspicion that the
difference is amplified due to de-TOAST'ing of the values in that text
column, in addition to the straight-up function execution time
differences.
Costing integer (or anything that doesn't require pointer maniuplations)
operations as cheaper than text-based operations also makes sense to me,
even though of course there's more things happening when we do these
comparisons than the simple CPU-level act of doing the cmp.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 835B) download
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: sfrost@snowman.net, tgl@sss.pgh.pa.us, nocera@gmail.com
Subject: Re: Inefficient filter order in query plan
In-Reply-To: <20140227182411.GR2921@tamriel.snowman.net>
* 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