Dear Francisco, team,
Thanks, absolutely right. Please excuse my omission.
Table field "data_in.ioground.tovaritelnica" is an index key. Therefore the first and third queries use it for 'order by' while the second does not since AS conceals the field name and the function call surely defeats the index.
Sincerely, Stefan
>-------- Оригинално писмо --------
>От: Francisco Olarte folarte@peoplecall.com
>Относно: Re: [BUGS] BUG #14491: Huge delay when specifying certain column names to select list expressions
>До: stefanov.sm@abv.bg
>Изпратено на: 11.01.2017 17:39
On Wed, Jan 11, 2017 at 2:10 PM, <
stefanov.sm@abv.bg> wrote:
> The following bug has been logged on the website:
> This is a real life example - a simple function that makes text CP1251-safe
> and 3 trivial queries. The first one is a test to determine whether the
> function incurs significant performance penalty (it does not). Execution
> time was 218 ms.
> Having specified explicit names to the select list expressions (these names
> are the same as the names of the underlying columns) execution time soared
> to 9.1 sec.
> The third query is the same as the second one except that the given
> expression names are not the same as the names of the underlying columns.
> The execution time was back again to the comfortable 219 ms.
It seems to me your 1st and 3rd queries are the same, the 2nd is not,
as order by uses the expresion, not the field, "tovaritelnica". (
https://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-ORDERBY : "Each expression can be the name or ordinal number of an output
column (SELECT list item), or it can be an arbitrary expression formed
from input-column values." )
1St:
> SELECT
... Output column
> data_in.clear_cp1251(tovaritelnica),
...
> ORDER BY tovaritelnica DESC
.. But ordered by field ( an expression ), as column is no named like it.
2Nd.
> SELECT
...
> data_in.clear_cp1251(tovaritelnica) AS tovaritelnica,
...
> ORDER BY tovaritelnica DESC
Now ordering by output column, it shadows the name.
3Rd:
> SELECT
...
> data_in.clear_cp1251(tovaritelnica) AS _tovaritelnica,
....
> ORDER BY tovaritelnica DESC
Again, ordering by expresion => field, no column.
Try specifying the column name as "data_in.ioground.tovaritelnica" in
the order by, or something similar. But remember 1st and 3rd query
select one thing but order by a different one.
Francisco Olarte.