Есть запрос вида:

explain analyse SELECT extract(epoch from setup_time) as setup_time, duration,
calling_id,
(select name || ' , ' || description from destinations,countries
WHERE countries.iso=destinations.country_iso and position(prefix in cdrs_connections.called_id_orig)=1
ORDER BY char_length(prefix) DESC LIMIT 1) as description,
called_id_orig,delay, name, destination
FROM cdrs_connections
INNER JOIN connections ON cdrs_connections.i_connection = connections.i_connection
WHERE cdrs_connections. i_connection in (select i_connection from connections where i_vendor='1')
and result = 0 and (setup_time >= timestamp 'epoch' + '1108137600 second')
and (setup_time <= timestamp 'epoch' + '1126454400 second')
ORDER BY setup_time DESC LIMIT 50 OFFSET 0;

который выплняется в 100 раз медленней, чем запророс, если из него убрать
вот этот пложеный зарос:
(select name || ' , ' || description from destinations,countries
WHERE countries.iso=destinations.country_iso and position(prefix in cdrs_connections.called_id_orig)=1
ORDER BY char_length(prefix) DESC LIMIT 1) as description,

может кто чего подскажет как его можно оптимизировать.
Спасибо.

с вложеным селектом:
Limit (cost=878.75..878.87 rows=50 width=105) (actual time=81881.965..81883.033 rows=50 loops=1)
-> Sort (cost=878.75..878.88 rows=52 width=105) (actual time=81881.950..81882.294 rows=50 loops=1)
Sort Key: date_part('epoch'::text, cdrs_connections.setup_time)
-> Hash Join (cost=2.30..877.27 rows=52 width=105) (actual time=3.307..81618.421 rows=18939 loops=1)
Hash Cond: ("outer".i_connection = "inner".i_connection)
-> Hash IN Join (cost=1.17..124.42 rows=52 width=82) (actual time=0.396..865.023 rows=18939 loops=1)
Hash Cond: ("outer".i_connection = "inner".i_connection)
-> Index Scan using cdrs_connections_2 on cdrs_connections (cost=0.00..122.47 rows=52 width=74) (actual time=0.286..650.609 rows=18939 loops=1)
Index Cond: ((setup_time >= ('2005-02-11 16:00:00'::timestamp without time zone)::timestamp with time zone) AND (setup_time <= ('2005-09-11 16:00:00'::timestamp without time zone)::timestamp with time zone))
Filter: (result = 0)
-> Hash (cost=1.14..1.14 rows=11 width=8) (actual time=0.089..0.089 rows=0 loops=1)
-> Seq Scan on connections (cost=0.00..1.14 rows=11 width=8) (actual time=0.008..0.049 rows=10 loops=1)
Filter: (i_vendor = 1::bigint)
-> Hash (cost=1.11..1.11 rows=11 width=47) (actual time=0.118..0.118 rows=0 loops=1)
-> Seq Scan on connections (cost=0.00..1.11 rows=11 width=47) (actual time=0.028..0.075 rows=11 loops=1)
SubPlan
-> Limit (cost=0.00..14.44 rows=1 width=35) (actual time=4.239..4.242 rows=1 loops=18939)
-> Nested Loop (cost=0.00..216.58 rows=15 width=35) (actual time=4.231..4.231 rows=1 loops=18939)
-> Index Scan Backward using len on destinations (cost=0.00..142.15 rows=15 width=27) (actual time=4.202..4.202 rows=1 loops=18939)
Filter: ("position"(($0)::text, (prefix)::text) = 1)
-> Index Scan using countries_pkey on countries (cost=0.00..4.94 rows=1 width=22) (actual time=0.010..0.010 rows=1 loops=18932)
Index Cond: (countries.iso = "outer".country_iso)
Total runtime: 81883.638 ms

без:

Limit (cost=127.94..128.07 rows=50 width=75) (actual time=1144.049..1145.079 rows=50 loops=1)
-> Sort (cost=127.94..128.07 rows=52 width=75) (actual time=1144.038..1144.368 rows=50 loops=1)
Sort Key: date_part('epoch'::text, cdrs_connections.setup_time)
-> Hash Join (cost=2.30..126.46 rows=52 width=75) (actual time=0.376..990.098 rows=18939 loops=1)
Hash Cond: ("outer".i_connection = "inner".i_connection)
-> Hash IN Join (cost=1.17..124.42 rows=52 width=52) (actual time=0.235..751.343 rows=18939 loops=1)
Hash Cond: ("outer".i_connection = "inner".i_connection)
-> Index Scan using cdrs_connections_2 on cdrs_connections (cost=0.00..122.47 rows=52 width=44) (actual time=0.127..561.139 rows=18939 loops=1)
Index Cond: ((setup_time >= ('2005-02-11 16:00:00'::timestamp without time zone)::timestamp with time zone) AND (setup_time <= ('2005-09-11 16:00:00'::timestamp without time zone)::timestamp with time zone))
Filter: (result = 0)
-> Hash (cost=1.14..1.14 rows=11 width=8) (actual time=0.088..0.088 rows=0 loops=1)
-> Seq Scan on connections (cost=0.00..1.14 rows=11 width=8) (actual time=0.007..0.048 rows=10 loops=1)
Filter: (i_vendor = 1::bigint)
-> Hash (cost=1.11..1.11 rows=11 width=47) (actual time=0.118..0.118 rows=0 loops=1)
-> Seq Scan on connections (cost=0.00..1.11 rows=11 width=47) (actual time=0.027..0.073 rows=11 loops=1)
Total runtime: 1163.073 ms
(записей: 16)

Funny_FalconПопробуй следующее


производительность упала процентов на 30

Funny_Falcon
Примечание : используя GIST индексы получится быстрее раза в полтора, но они будут полностью конкурентными и журналируемыми только в 8.1. А так, устанавливаешь btree_gist и пишешь:



btree_gist now support int2, int8, float4, float8 !
что-то ты перепутал

Funny_Falcon
Кроме того, я на этом форуме выкладывал правленные мною файлы BTree, правда нужно его проверить на соответствие новым версиям. Индекс для B в A LIKE B || '%'
Там, на самом деле, небольшие справления, а выигрыш большой.

а патч есть на это дело?

Funny_FalconПопробуй следующее
Примечание : используя GIST индексы получится быстрее раза в полтора, но они будут полностью конкурентными и журналируемыми только в 8.1. А так, устанавливаешь btree_gist и пишешь:


Поставил постгрис 8.1 и создал gist индекс.
Производительность просто супер.

Теперь хотелось бы что Вы немножко пояснили по синтаксису и как оно,собственно, работает.

Огромное спасибо!!!

LeXa NalBat salx Funny_FalconПопробуй следующее
Примечание : используя GIST индексы получится быстрее раза в полтора, но они будут полностью конкурентными и журналируемыми только в 8.1. А так, устанавливаешь btree_gist и пишешь:


Поставил постгрис 8.1 и создал gist индекс.
Производительность просто супер.Киньте пожалуйста EXPLAIN ANALYZE.

Limit (cost=112314.60..112314.73 rows=50 width=246)
-> Sort (cost=112314.60..112318.77 rows=1667 width=246)
Sort Key: date_part('epoch'::text, cdrs_connections.setup_time)
-> Nested Loop (cost=530.82..112225.39 rows=1667 width=246)
-> Hash Join (cost=1.15..2.33 rows=1 width=196)
Hash Cond: ("outer".i_connection = "inner".i_connection)
-> Seq Scan on connections (cost=0.00..1.11 rows=11 width=188)
-> Hash (cost=1.15..1.15 rows=1 width=8)
-> HashAggregate (cost=1.14..1.15 rows=1 width=8)
-> Seq Scan on connections (cost=0.00..1.14 rows=1 width=8)
Filter: (i_vendor = 1::bigint)
-> Bitmap Heap Scan on cdrs_connections (cost=529.67..5539.80 rows=2292 width=74)
Recheck Cond: ((cdrs_connections.i_connection = "outer".i_connection) AND (cdrs_connections.setup_time >= '2005-02-11 16:00:00'::timestamp without time zone) AND (cdrs_connections.setup_time <= '2005-09-11 16:00:00'::timestamp without time zone) AND (cdrs_connections.result = 0))
-> Bitmap Index Scan on cdrs_connections_1 (cost=0.00..529.67 rows=2292 width=0)
Index Cond: ((cdrs_connections.i_connection = "outer".i_connection) AND (cdrs_connections.setup_time >= '2005-02-11 16:00:00'::timestamp without time zone) AND (cdrs_connections.setup_time <= '2005-09-11 16:00:00'::timestamp without time zone) AND (cdrs_connections.result = 0))
SubPlan
-> Limit (cost=63.97..63.98 rows=1 width=70)
-> Sort (cost=63.97..64.76 rows=314 width=70)
Sort Key: char_length((destinations.prefix)::text)
-> Hash Join (cost=11.90..50.95 rows=314 width=70)
Hash Cond: ("outer".country_iso = "inner".iso)
-> Bitmap Heap Scan on destinations (cost=6.88..36.38 rows=314 width=27)
Recheck Cond: ((((prefix)::text || 'я'::text) > ($0)::text) AND ((prefix)::text <= ($0)::text))
-> Bitmap Index Scan on ix_pref (cost=0.00..6.88 rows=314 width=0)
Index Cond: ((((prefix)::text || 'я'::text) > ($0)::text) AND ((prefix)::text <= ($0)::text))
-> Hash (cost=4.41..4.41 rows=241 width=57)
-> Seq Scan on countries (cost=0.00..4.41 rows=241 width=57)