Запрос такой:

explain analyze select * from xprop_turk
where
creation_date > ('2005-01-01 00:00:00'::timestamp + '1 month'::interval)
and xstring_vector @@ to_tsquery('default','haber & entire & cnn')
and xpath_vector @@ to_tsquery('default','16667396')
limit 100;

таблица xprop_turk имеет 4 выведенных таблицы, разделенных по году 4, 5, 6, 7

Не могу понять почему в плане видно, что сервре пытается искать по всем частям, если я явно указал дату, вроде д.б рыть в xprop_turk_05...
Как можно оптимизнуть? А то шибко долго роется...


QUERY PLAN
Limit (cost=0.00..629.60 rows=9 width=911) (actual time=217.484..1513.975 rows=65 loops=1)
-> Result (cost=0.00..629.60 rows=9 width=911) (actual time=217.483..1513.934 rows=65 loops=1)
-> Append (cost=0.00..629.60 rows=9 width=911) (actual time=217.480..1513.876 rows=65 loops=1)
-> Index Scan using xprop_turk_xstring_idx on xprop_turk (cost=0.00..8.27 rows=1 width=252) (actual time=0.017..0.017 rows=0 loops=1)
Index Cond: (xstring_vector @@ '''haber'' & ''entir'' & ''cnn'''::tsquery)
Filter: ((creation_date > '2005-02-01 00:00:00'::timestamp without time zone) AND (xstring_vector @@ '''haber'' & ''entir'' & ''cnn'''::tsquery) AND (xpath_vector @@ '''16667396'''::tsquery))
-> Bitmap Heap Scan on xprop_turk_05 xprop_turk (cost=92.02..96.04 rows=1 width=911) (actual time=217.462..230.056 rows=42 loops=1)
Filter: ((creation_date > '2005-02-01 00:00:00'::timestamp without time zone) AND (xstring_vector @@ '''haber'' & ''entir'' & ''cnn'''::tsquery) AND (xpath_vector @@ '''16667396'''::tsquery))
-> BitmapAnd (cost=92.02..92.02 rows=1 width=0) (actual time=217.231..217.231 rows=0 loops=1)
-> Bitmap Index Scan on xprop_turk_xpath_05_idx (cost=0.00..43.85 rows=970 width=0) (actual time=101.836..101.836 rows=10517 loops=1)
Index Cond: (xpath_vector @@ '''16667396'''::tsquery)
-> Bitmap Index Scan on xprop_turk_xstring_05_idx (cost=0.00..47.92 rows=970 width=0) (actual time=113.445..113.445 rows=1466 loops=1)
Index Cond: (xstring_vector @@ '''haber'' & ''entir'' & ''cnn'''::tsquery)
-> Bitmap Heap Scan on xprop_turk_06 xprop_turk (cost=451.83..475.88 rows=6 width=822) (actual time=1169.466..1184.978 rows=23 loops=1)
Filter: ((creation_date > '2005-02-01 00:00:00'::timestamp without time zone) AND (xstring_vector @@ '''haber'' & ''entir'' & ''cnn'''::tsquery) AND (xpath_vector @@ '''16667396'''::tsquery))
-> BitmapAnd (cost=451.83..451.83 rows=6 width=0) (actual time=1169.141..1169.141 rows=0 loops=1)
-> Bitmap Index Scan on xprop_turk_xstring_06_idx (cost=0.00..211.66 rows=5565 width=0) (actual time=499.815..499.815 rows=1947 loops=1)
Index Cond: (xstring_vector @@ '''haber'' & ''entir'' & ''cnn'''::tsquery)
-> Bitmap Index Scan on xprop_turk_xpath_06_idx (cost=0.00..239.92 rows=5565 width=0) (actual time=668.478..668.478 rows=26047 loops=1)
Index Cond: (xpath_vector @@ '''16667396'''::tsquery)
-> Bitmap Heap Scan on xprop_turk_07 xprop_turk (cost=45.39..49.41 rows=1 width=706) (actual time=98.779..98.779 rows=0 loops=1)
Filter: ((creation_date > '2005-02-01 00:00:00'::timestamp without time zone) AND (xstring_vector @@ '''haber'' & ''entir'' & ''cnn'''::tsquery) AND (xpath_vector @@ '''16667396'''::tsquery))
-> BitmapAnd (cost=45.39..45.39 rows=1 width=0) (actual time=93.726..93.726 rows=0 loops=1)
-> Bitmap Index Scan on xprop_turk_xstring_07_idx (cost=0.00..20.56 rows=554 width=0) (actual time=45.098..45.098 rows=205 loops=1)
Index Cond: (xstring_vector @@ '''haber'' & ''entir'' & ''cnn'''::tsquery)
-> Bitmap Index Scan on xprop_turk_xpath_07_idx (cost=0.00..24.58 rows=554 width=0) (actual time=48.577..48.577 rows=2778 loops=1)
Index Cond: (xpath_vector @@ '''16667396'''::tsquery)
Total runtime: 1514.112 ms

LeXa NalBatпод условие creation_date>2005 подходят 2005, 2006, 2007,..

100%

учтя ошибки, вот запрос, и вот план. Пробелма в том, что долго выполняется запрос. 6 секунд

что можно сделать?

select
--*
node_id, parent_id, node_type, node_subtype from xprop_turk
where
creation_date > ('2006-01-01 00:00:00'::timestamp + '1 month'::interval)
and creation_date <= ('2006-12-31 00:00:00'::timestamp)
and xstring_vector @@ to_tsquery('default','polis & entire')
and xpath_vector @@ to_tsquery('default','4054126')
and xaccess_vector @@ to_tsquery('default','311')
limit 100;


Limit (cost=0.00..484.18 rows=2 width=40) (actual time=19963.810..20160.487 rows=100 loops=1)
-> Result (cost=0.00..484.18 rows=2 width=40) (actual time=19963.807..20160.396 rows=100 loops=1)
-> Append (cost=0.00..484.18 rows=2 width=40) (actual time=19963.806..20160.304 rows=100 loops=1)
-> Index Scan using xprop_turk_xstring_idx on xprop_turk (cost=0.00..8.28 rows=1 width=40) (actual time=0.017..0.017 rows=0 loops=1)
Index Cond: (xstring_vector @@ '''poli'' & ''entir'''::tsquery)
Filter: ((creation_date > '2006-02-01 00:00:00'::timestamp without time zone) AND (creation_date <= '2006-12-31 00:00:00'::timestamp without time zone) AND (xstring_vector @@ '''poli'' & ''entir'''::tsquery) AND (xpath_vector @@ '''4054126'''::tsquery) AND (xaccess_vector @@ '''311'''::tsquery))
-> Bitmap Heap Scan on xprop_turk_06 xprop_turk (cost=451.83..475.91 rows=1 width=40) (actual time=19963.788..20160.211 rows=100 loops=1)
Filter: ((creation_date > '2006-02-01 00:00:00'::timestamp without time zone) AND (creation_date <= '2006-12-31 00:00:00'::timestamp without time zone) AND (xstring_vector @@ '''poli'' & ''entir'''::tsquery) AND (xpath_vector @@ '''4054126'''::tsquery) AND (xaccess_vector @@ '''311'''::tsquery))
-> BitmapAnd (cost=451.83..451.83 rows=6 width=0) (actual time=19962.773..19962.773 rows=0 loops=1)
-> Bitmap Index Scan on xprop_turk_xstring_06_idx (cost=0.00..211.66 rows=5565 width=0) (actual time=761.240..761.240 rows=21750 loops=1)
Index Cond: (xstring_vector @@ '''poli'' & ''entir'''::tsquery)
-> Bitmap Index Scan on xprop_turk_xpath_06_idx (cost=0.00..239.92 rows=5565 width=0) (actual time=19195.868..19195.868 rows=4703949 loops=1)
Index Cond: (xpath_vector @@ '''4054126'''::tsquery)

LeXa NalBat Winnipuh-> Bitmap Index Scan on xprop_turk_xstring_06_idx (rows=5565) (actual rows=21750)
-> Bitmap Index Scan on xprop_turk_xpath_06_idx (rows=5565) (actual rows=4703949)сделайте vacuum analyze

PS в запросе limit без order by

а что - лучше при использовании лимита указывать ордер?

LeXa NalBat Winnipuhа что - лучше при использовании лимита указывать ордер?цитирую вас :) тынц

точно... :-) ...

ордер не улучшает, увы...

вот что интересно еще, такой запрос выполняется 1-2 секунды:

explain analyze
select
--*
node_id, parent_id, node_type, node_subtype
from xprop_turk_06
where
xstring_vector @@ to_tsquery('default','polis & entire')
--and xpath_vector @@ to_tsquery('default','4054126')
--and xaccess_vector @@ to_tsquery('default','311')
and creation_date > ('2006-01-01 00:00:00'::timestamp + '1 month'::interval)
and creation_date <= ('2006-12-31 00:00:00'::timestamp)
--order by creation_date
limit 100;

---------

как только открываю комментарий и добавляю хотя бы один критерий , то секунд 20.

--and xpath_vector @@ to_tsquery('default','4054126')
--and xaccess_vector @@ to_tsquery('default','311')

судя по планам сначала отрабатывается один из этих двух критериев, а потом уже xstring_vector @@ to_tsquery('default','polis & entire')

Как можно указать (если можно), чтобы первым выполнялся xstring_vector @@ to_tsquery('default','polis & entire') ?

LeXa NalBat Winnipuhордер не улучшает, увы...ускорять он и не должен

Winnipuhвот что интересно еще, такой запрос выполняется 1-2 секунды:

where
xstring_vector @@ to_tsquery('default','polis & entire')
--and xpath_vector @@ to_tsquery('default','4054126')
--and xaccess_vector @@ to_tsquery('default','311')

как только открываю комментарий и добавляю хотя бы один критерий , то секунд 20.

судя по планам сначала отрабатывается один из этих двух критериев, а потом уже xstring_vector @@ to_tsquery('default','polis & entire')покажите планы :)

так vacuum analyze улучшил предсказания постгреса относительно кол-ва строк?
-> Bitmap Index Scan on xprop_turk_xstring_06_idx (rows=5565)
-> Bitmap Index Scan on xprop_turk_xpath_06_idx (rows=5565)

WinnipuhКак можно указать (если можно), чтобы первым выполнялся xstring_vector @@ to_tsquery('default','polis & entire')?мне кажется, надо попробовать добиться плана IndexScan(xstring_vector), Filter(xpath_vector,xaccess_vector).
для этого сначала попробовать разобраться с ?неправильной? оценкой кол-ва строк. (см. выше)

сделал vacuum full analyze, такое же количество и осталось.


я написал о своем предположении здесь
http://www.sql.ru/forum/actualthread.aspx?tid=486482
коротко так:

есть запрос select * from t where
where
xstring_vector @@ to_tsquery('default','polis & entire') -- здесь 700000
and
xpath_vector @@ to_tsquery('default','4054126') -- здесь 7 000 000
limit 100;


и как бы я ни крутил, не переставлял критерий: всегда выбираются оба подмножества, а потом делается пересечение

так ли это? и можно ли бороться с этим явлением?

LeXa NalBat Winnipuhсделал vacuum full analyze, такое же количество и осталось.то есть постгрес не умеет оценивать кол-во строк (или вычислять статистику распределения) для выборки по полнотекстовому GIST (используется у вас) индексу? может быть, как вам посоветовали, попробовать GIN? для GIN постгрес умеет это оценивать?

Winnipuhи как бы я ни крутил, не переставлял критерий: всегда выбираются оба подмножества, а потом делается пересечение

так ли это?я вам уже ответил в другой теме. да, в ваших планах получается так. "каждый BitmapIndexScan отрабатывает полностью (без учета limit)."

Winnipuhи можно ли бороться с этим явлением?я вам уже ответил в другой теме. "пытаться запинать на другой план." так как vacuum analyze не помог, попробуйте set enable_bitmapscan to off.

мало того, что
"каждый BitmapIndexScan отрабатывает полностью (без учета limit)." так еще и без учета уже полученного подмножества, т.е. если А and Б, и А уже получено, то в резльтате может быть только меньше, а в моем случае А=700 000, и Б честно ищет 7 000 000 после А.

Конечно, если бы лимит учесть совсем было бы красиво.

что сделал:
1. увеличил по совету Бартунова память до 1ГБ (всего 4ГБ на сервере)
2. перестроил индексы GIN
3. результат стал странным, при выполнении одного и того же запроса получал то 20 сек (как и для индексов GIST). так и 1 сек.
4. выполнил такой запрос
set enable_bitmapscan to off;
select node_id, parent_id, node_type, node_subtype
from xprop_turk
where
creation_date > ('2006-01-01 00:00:00'::timestamp + '1 month'::interval)
and creation_date <= ('2006-12-31 00:00:00'::timestamp)
and xstring_vector @@ to_tsquery('default','polis & entire')
and xpath_vector @@ to_tsquery('default','4054126') --4054126 wenig -- 4095961 viel

limit 100;

общий запрос выполняется 1 сек. !!!!!!! шайтан!

Спасибо LExa!

так что же, какой вывод - bitmapscan = off для севрера?