Обсуждение: [BUGS] BUG #14491: Huge delay when specifying certain column names to selectlist expressions

The following bug has been logged on the website:

Bug reference:      14491
Logged by:          Stefan Stefanov
Email address:      stefanov.sm@abv.bg
PostgreSQL version: 9.2.16
Operating system:   Windows, x86
Description:

Dear All,

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.

Performance influence/dependancy of given names vs. column names?. Couldn't
find anything mentioned in the documentation
(https://www.postgresql.org/docs/9.2/static/sql-select.html).

CREATE OR REPLACE FUNCTION data_in.clear_cp1251(t text) RETURNS text AS --
Remove non-CP1251 characters
$$
 SELECT regexp_replace
 (
  t, 
  '(?x)
  [^
   \u0020-\u007F

\u00A0\u00A4\u00A6\u00A7\u00A9\u00AB\u00AC\u00AD\u00AE\u00B0\u00B1\u00B5\u00B6\u00B7\u00BB
   \u0401-\u040C\u040E\u040F
   \u0410-\u044F
   \u0451-\u045C\u045E\u045F


\u0490\u0491\u2013\u2014\u2018\u2019\u201A\u201C\u201D\u201E\u2020\u2021\u2022\u2026\u2030\u2039\u203A\u20AC\u2116\u2122
  ]',
  '?', 'g'
 );
$$
LANGUAGE sql IMMUTABLE STRICT COST 10;

SELECT
    data_in.clear_cp1251(klientski_id_nomer),
    data_in.clear_cp1251(sadarzhanie_na_opakovkata),
    data_in.clear_cp1251(izhodiasht_nomer),
    data_in.clear_cp1251(zabelezhka),
    data_in.clear_cp1251(kod_platets),
    data_in.clear_cp1251(kod_poluchatel),
    data_in.clear_cp1251(stikeri),
    data_in.clear_cp1251(ofis_na_poluchavane),
    data_in.clear_cp1251(nomer_podatel),
    data_in.clear_cp1251(poluchatel),
    data_in.clear_cp1251(litse_za_kontakti),
    data_in.clear_cp1251(telefon_gsm),
    data_in.clear_cp1251(naseleno_miasto),
    data_in.clear_cp1251(pk),
    data_in.clear_cp1251(kvartal),
    data_in.clear_cp1251(ulitsa),
    data_in.clear_cp1251(nomer_ulitsa),
    data_in.clear_cp1251(blok),
    data_in.clear_cp1251(vhod),
    data_in.clear_cp1251(apartament),
    data_in.clear_cp1251(kod_na_stranata),
    data_in.clear_cp1251(tovaritelnica),
    data_in.clear_cp1251(bulstat_podatel),
    data_in.clear_cp1251(kod_poluchatel_np),
    data_in.clear_cp1251(zaiavka_data)
 FROM data_in.ioground
 ORDER BY tovaritelnica DESC
 LIMIT 1000; -- Execution time 218 ms

-- Regexp find/replace \(([^\)]+)\) --> ($1) AS $1
SELECT
    data_in.clear_cp1251(klientski_id_nomer) AS klientski_id_nomer,
    data_in.clear_cp1251(sadarzhanie_na_opakovkata) AS
sadarzhanie_na_opakovkata,
    data_in.clear_cp1251(izhodiasht_nomer) AS izhodiasht_nomer,
    data_in.clear_cp1251(zabelezhka) AS zabelezhka,
    data_in.clear_cp1251(kod_platets) AS kod_platets,
    data_in.clear_cp1251(kod_poluchatel) AS kod_poluchatel,
    data_in.clear_cp1251(stikeri) AS stikeri,
    data_in.clear_cp1251(ofis_na_poluchavane) AS ofis_na_poluchavane,
    data_in.clear_cp1251(nomer_podatel) AS nomer_podatel,
    data_in.clear_cp1251(poluchatel) AS poluchatel,
    data_in.clear_cp1251(litse_za_kontakti) AS litse_za_kontakti,
    data_in.clear_cp1251(telefon_gsm) AS telefon_gsm,
    data_in.clear_cp1251(naseleno_miasto) AS naseleno_miasto,
    data_in.clear_cp1251(pk) AS pk,
    data_in.clear_cp1251(kvartal) AS kvartal,
    data_in.clear_cp1251(ulitsa) AS ulitsa,
    data_in.clear_cp1251(nomer_ulitsa) AS nomer_ulitsa,
    data_in.clear_cp1251(blok) AS blok,
    data_in.clear_cp1251(vhod) AS vhod,
    data_in.clear_cp1251(apartament) AS apartament,
    data_in.clear_cp1251(kod_na_stranata) AS kod_na_stranata,
    data_in.clear_cp1251(tovaritelnica) AS tovaritelnica,
    data_in.clear_cp1251(bulstat_podatel) AS bulstat_podatel,
    data_in.clear_cp1251(kod_poluchatel_np) AS kod_poluchatel_np,
    data_in.clear_cp1251(zaiavka_data) AS zaiavka_data
 FROM data_in.ioground
 ORDER BY tovaritelnica DESC
 LIMIT 1000; -- Execution time 9.1 s

-- Regexp find/replace \(([^\)]+)\) --> ($1) AS _$1
SELECT
    data_in.clear_cp1251(klientski_id_nomer) AS klientski_id_nomer,
    data_in.clear_cp1251(sadarzhanie_na_opakovkata) AS
_sadarzhanie_na_opakovkata,
    data_in.clear_cp1251(izhodiasht_nomer) AS _izhodiasht_nomer,
    data_in.clear_cp1251(zabelezhka) AS _zabelezhka,
    data_in.clear_cp1251(kod_platets) AS _kod_platets,
    data_in.clear_cp1251(kod_poluchatel) AS _kod_poluchatel,
    data_in.clear_cp1251(stikeri) AS _stikeri,
    data_in.clear_cp1251(ofis_na_poluchavane) AS _ofis_na_poluchavane,
    data_in.clear_cp1251(nomer_podatel) AS _nomer_podatel,
    data_in.clear_cp1251(poluchatel) AS _poluchatel,
    data_in.clear_cp1251(litse_za_kontakti) AS _litse_za_kontakti,
    data_in.clear_cp1251(telefon_gsm) AS _telefon_gsm,
    data_in.clear_cp1251(naseleno_miasto) AS _naseleno_miasto,
    data_in.clear_cp1251(pk) AS _pk,
    data_in.clear_cp1251(kvartal) AS _kvartal,
    data_in.clear_cp1251(ulitsa) AS _ulitsa,
    data_in.clear_cp1251(nomer_ulitsa) AS _nomer_ulitsa,
    data_in.clear_cp1251(blok) AS _blok,
    data_in.clear_cp1251(vhod) AS _vhod,
    data_in.clear_cp1251(apartament) AS _apartament,
    data_in.clear_cp1251(kod_na_stranata) AS _kod_na_stranata,
    data_in.clear_cp1251(tovaritelnica) AS _tovaritelnica,
    data_in.clear_cp1251(bulstat_podatel) AS _bulstat_podatel,
    data_in.clear_cp1251(kod_poluchatel_np) AS _kod_poluchatel_np,
    data_in.clear_cp1251(zaiavka_data) AS _zaiavka_data
 FROM data_in.ioground
 ORDER BY tovaritelnica DESC
 LIMIT 1000; -- Execution time 219 ms

Sincerely,
Stefan


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.