Date: Thu, 27 Feb 2014 13:24:11 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Tom Coogan <nocera@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Inefficient filter order in query plan
Message-ID: <20140227182411.GR2921@tamriel.snowman.net>
References: 
 <CAN07wvLhJhWApx2uGiyeOeCMtXjLw8W84vNNxrhcNvLxKjhTdQ@mail.gmail.com>
 <1633.1393520657@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature"; boundary="1O0MF4Q5XkVUDSHn"
Content-Disposition: inline
In-Reply-To: <1633.1393520657@sss.pgh.pa.us>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--1O0MF4Q5XkVUDSHn
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* 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.
>=20
> 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.
>=20
> 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

--1O0MF4Q5XkVUDSHn
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)

iQIcBAEBAgAGBQJTD4LLAAoJEO1sijiDR2RVtkwP/A0KC6qXFSzZwORwOM19w33h
1PFR7nIv3WbYFMYMWBFPfU8VT3Gi4eUMN9bTjF3LiiYRtY+/Eg26ChPN4wM0RFwn
FArAmE89x0kaiOETxPHExTI3UIW0C702J7RfA05whiM48vowKyc1jJF8OJiFNB7M
JtiYTrThNxaKjqtOnlpwnpIPEP+rkMAe4mY9Cy6207yN2AA0Z1dVgqZcF9s5lgYF
2WMs2AwXST9hOVsc3p5of3kg+Bpv0jmaaDHdICs9zG9atqt8NovQYb/KDgXVkmq0
xEnMJPInwHHYBqee6TZeJv4TvjhFlvdsxXiXxppmhtg5tuan85mpr+RU6ViImKg5
BVtgjEuxUOwMdmumB3i71PUxhcYo5R9PThzsUS6pdM/nP4E1IMspksJ+CZfk5Oqp
xePe+PmGNAwogxsOmo9IfNWAmk6VGPryFhVlvE8E/wRfu8aSFKAMSeL50wjMs38U
gFrZLPTSOwjMDfOBupCr4Ht3rMaasMdBatDkUMSKLkdrfglA8zD2dTqt7rAIpnec
Ikx/ZM2KiU8pPuj7pxJLo+oQaoq6wevd4dEmD0HiihH79w/icy9qnd8gN/7LVmuE
qkunN+W2x9dOBPzT75TNpd2ue0Lyamu7+W1HM2omv1ELzzCDFgayCSHWO4tP0N5P
/ILFHMM5Su5FTteVmMIO
=/h9v
-----END PGP SIGNATURE-----

--1O0MF4Q5XkVUDSHn--