On Thu, Feb 27, 2014 at 12:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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.
>
Thanks Tom but why would strict equality checking (e.g. model =
'User') have the same cost as LIKE operations which (may) have to do
pattern matching? I understand from the optimizer's perspective that
the actual cost could be equivalent depending upon what value is
searched (and the optimizer wouldn't know that value ahead of time).
But doesn't the potential for pattern matching warrant some difference
in cost? From my experience, LIKE is almost always used with some
form of pattern match in the supplied value.
On Thu, Feb 27, 2014 at 1:24 PM, Stephen Frost <sfrost@snowman.net> wrote:
>
> 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.
Let me know if this is the wrong way to find this information but it
doesn't appear that any values in this particular table are TOAST'ed:
SELECT oid, relname, reltoastrelid, relpages FROM pg_class WHERE
relname = 'audit_trail' OR oid = 7971231;
oid | relname | reltoastrelid | relpages
---------+------------------+---------------+----------
7971228 | audit_trail | 7971231 | 150502
7971231 | pg_toast_7971228 | 0 | 0