[BUGS] BUG #14491: Huge delay when specifying certain column names to selectlist expressions - Mailing list pgsql-bugs

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