Производительность запросов с конкатенацией и LIKE

Может ли кто-нибудь объяснить разницу в производительности между этими тремя запросами?

concat() функция:

explain analyze 
select * from person 
where (concat(last_name, ' ', first_name, ' ', middle_name) like '%Ива%');

Seq Scan on person  (cost=0.00..4.86 rows=1 width=15293) (actual time=0.032..0.140 rows=6 loops=1)
  Filter: (pg_catalog.concat(last_name, ' ', first_name, ' ', middle_name) ~~ '%Ива%'::text)
Total runtime: 0.178 ms

Стандартная конкатенация SQL с ||:

explain analyze 
select * from person 
where ((last_name || ' ' || first_name || ' ' || middle_name) like '%Ива%');

Seq Scan on person  (cost=0.00..5.28 rows=1 width=15293) (actual time=0.023..0.080 rows=6 loops=1)
  Filter: ((((((last_name)::text || ' '::text) || (first_name)::text) || ' '::text) || (middle_name)::text) ~~ '%Ива%'::text)
Total runtime: 0.121 ms

Поля поиска отдельно:

explain analyze 
select * from person 
where (last_name like '%Ива%') or (first_name like '%Ива%') or (middle_name like '%Ива%');

Seq Scan on person  (cost=0.00..5.00 rows=1 width=15293) (actual time=0.018..0.060 rows=6 loops=1)
  Filter: (((last_name)::text ~~ '%Ива%'::text) OR ((first_name)::text ~~ '%Ива%'::text) OR ((middle_name)::text ~~ '%Ива%'::text))
Total runtime: 0.097 ms

Почему concat() самый медленный, а несколько like условий быстрее?


postgresql sql concatenation pattern-matching postgresql-performance
person Yegor Koldov    schedule 13.04.2015    source источник
comment
В чем именно заключается ваш вопрос?   -  person a_horse_with_no_name    schedule 13.04.2015
comment
Почему pg.concat самый медленный, а несколько like быстрее?   -  person Yegor Koldov    schedule 13.04.2015
comment
@ma3a попробуйте last_name||' '||first_name||' '||middle_name (думаю, в вашем случае ручная конкатенация лучше, чем concat())   -  person Vivek S.    schedule 13.04.2015
comment
@vivek, посмотрите второй запрос, он немного быстрее, чем pg.concat, и это странно, потому что первый и второй запросы почти одинаковы. Это не производственный код, мне просто интересно, почему эти запросы работают медленно   -  person Yegor Koldov    schedule 13.04.2015
comment
@ma3a, потому что concat() является функцией, когда вы выполняете свой выбранный postgres вызов, который concat() для операции   -  person Vivek S.    schedule 13.04.2015
comment
@vivek, так это накладные расходы на вызов функции в каждой строке?   -  person Yegor Koldov    schedule 13.04.2015
comment
@ ma3a В вашем случае это накладные расходы.   -  person Vivek S.    schedule 13.04.2015
comment
Повторно запустите эти операторы в другом порядке. Различия во времени выполнения достаточно малы, чтобы быть вызванными кэшированием базовых данных.   -  person a_horse_with_no_name    schedule 13.04.2015
comment
@a_horse_with_no_name, я сделал повтор в другом порядке (в обратном порядке), и никаких изменений: 0,096, 0,120, 0,182   -  person Yegor Koldov    schedule 13.04.2015
comment
Тест на таблице всего с 6 строками мало что значит. И этот запрос настолько быстр, что любая мелочь вашего компьютера может быть ответственна за разницу во времени.   -  person Frank Heikens    schedule 13.04.2015
comment
Таблица @Frank Heikens не 6 строк, select count(*) from person производит 43   -  person Yegor Koldov    schedule 13.04.2015
comment
@ma3a: А это имеет значение? Это все еще почти пустая таблица.... Создайте миллион записей и снова запустите свои тесты. Последовательное сканирование, выполняемое в течение миллисекунды, никогда не может быть медленным, несмотря ни на что.   -  person Frank Heikens    schedule 13.04.2015

Ответы (3)


arrow_upward
6
arrow_downward

Хотя это и не конкретный ответ, следующее может помочь вам сделать некоторые выводы:

  1. Вызов concat для объединения трех строк или использование оператора || приводит к тому, что postgres приходится выделять новый буфер для хранения объединенной строки, а затем копировать в него символы. Это нужно сделать для каждой строки. Затем буфер должен быть освобожден в конце.

  2. В случае, когда вы выполняете операцию ИЛИ вместе с тремя условиями, postgres может потребоваться только оценить только одно или, может быть, два из них, чтобы решить, должен ли он включать строку.

  3. Возможно, что вычисление выражения с помощью оператора || может быть более эффективным или, возможно, более легко оптимизируемым по сравнению с вызовом функции concat. Я не удивлюсь, если обнаружу, что для внутренних операторов существует особая обработка регистров.

  4. Как упоминалось в комментариях, ваша выборка слишком мала, чтобы делать правильные выводы. На уровне долей миллисекунды другие шумовые факторы могут исказить результат.

person harmic    schedule 13.04.2015
comment
из нескольких сотен миллисекунд тестовые примеры еще хуже, все тесты выполнялись в течение миллисекунды, самый медленный тест занял всего 0,178 миллисекунды. - person Frank Heikens; 13.04.2015

arrow_upward
3
arrow_downward

То, что вы наблюдали до сих пор, интересно, но едва ли важно. Незначительные накладные расходы на объединение строк.

Намного более важная разница между этими выражениями не проявляется в вашем минимальном тестовом примере без индексов.

Первые два примера не являются sargable (если только вы не создадите специальный индекс выражения):

where concat(last_name, ' ', first_name, ' ', middle_name) like '%Ива%'
where (last_name || ' ' || first_name || ' ' || middle_name) like '%Ива%'

Пока этот:

where last_name like '%Ива%' or first_name like '%Ива%' or middle_name like '%Ива%'

То есть он может использовать простой индекс триграммы для большого эффекта (порядок столбцов не важен в индексе GIN):

CREATE INDEX some_idx ON person USING gin (first_name  gin_trgm_ops
                                         , middle_name gin_trgm_ops
                                         , last_name   gin_trgm_ops);

Инструкции:

Неправильный тест, если возможен NULL

concat() обычно немного дороже. чем простая конкатенация строк с ||. Это также отличается: если какая-либо из входных строк имеет значение NULL, конкатенированный результат также будет NULL во втором случае, но не в первом случае, поскольку concat() просто игнорирует значения NULL, но вы бы все равно получите бесполезный символ пробела в результате.

Детальное объяснение:

Если вы ищете чистое, элегантное выражение (примерно той же стоимости), используйте concat_ws() вместо этого:

concat_ws( ' ', last_name, first_name, middle_name)
person Erwin Brandstetter    schedule 13.04.2015

arrow_upward
1
arrow_downward

Этот запрос имеет накладные расходы для вызова функции в каждой строке

explain analyze 
select * from person 
where (concat(last_name, ' ', first_name, ' ', middle_name) like '%Ива%');

этот запрос быстрее, потому что дополнительная операция не выполняется

explain analyze 
select * from person 
where (last_name like '%Ива%') or (first_name like '%Ива%') or (middle_name like '%Ива%');
person Yegor Koldov    schedule 13.04.2015