здравствуйте,
есть запрос вида

SELECT t.btime, t.etime, l.link_id, m.ni, m.opc, m.dpc, a.cgssn, a.cdssn, s.count
FROM time_intervals_css7 AS t, mtp_addr AS m, link_addr AS l, sccp_addr AS a, sccp_traf_stat AS s
WHERE t.time_id = s.time_id
AND l.link_addr_id = s.link_addr_id AND m.mtp_addr_id = s.mtp_addr_id
AND a.sccp_addr_id = s.sccp_addr_id
AND ( t.btime BETWEEN '2007-8-1 0:0:0' AND '2007-8-15 0:0:0' )
AND ( t.etime BETWEEN '2007-8-1 0:0:0' AND '2007-8-15 0:0:0' )
AND l.link_id IN ( 'SL1714636915', 'SL1957747793', 'SL1025202362', 'SL1303455736', 'SL1540383426', 'SL1365180540', 'SL1967513926', 'SL1101513929');


------------------------------------------------------ QUERY PLAN------------------------------------
Hash Join (cost=14450.02..1406914.51 rows=2151318 width=45) (actual time=1032.317..106110.888 rows=1800406 loops=1)
Hash Cond: ("outer".mtp_addr_id = "inner".mtp_addr_id)
-> Hash Join (cost=14447.89..1374642.61 rows=2151318 width=43) (actual time=1023.588..103760.784 rows=1800406 loops=1)
Hash Cond: ("outer".sccp_addr_id = "inner".sccp_addr_id)
-> Hash Join (cost=25.65..1271534.71 rows=2151318 width=43) (actual time=52.355..98747.973 rows=1800406 loops=1)
Hash Cond: ("outer".link_addr_id = "inner".link_addr_id)
-> Hash Join (cost=23.79..1206993.32 rows=8605271 width=32) (actual time=16.577..88618.967 rows=21425506 loops=1)
Hash Cond: ("outer".time_id = "inner".time_id)
-> Seq Scan on sccp_traf_stat s (cost=0.00..858645.88 rows=52454188 width=20) (actual time=0.018..45321.935 rows=52454189 loops=1)
-> Hash (cost=23.11..23.11 rows=272 width=20) (actual time=16.545..16.545 rows=672 loops=1)
-> Index Scan using time_intervals_css7_etime_idx on time_intervals_css7 t (cost=0.00..23.11 rows=272 width=20) (actual time=15.034..16.026 rows=672 loops=1)
Index Cond: ((etime >= '2007-08-01 00:00:00+04'::timestamp with time zone) AND (etime <= '2007-08-15 00:00:00+04'::timestamp with time zone))
Filter: ((btime >= '2007-08-01 00:00:00+04'::timestamp with time zone) AND (btime <= '2007-08-15 00:00:00+04'::timestamp with time zone))
-> Hash (cost=1.84..1.84 rows=7 width=19) (actual time=9.801..9.801 rows=8 loops=1)
-> Seq Scan on link_addr l (cost=0.00..1.84 rows=7 width=19) (actual time=9.754..9.792 rows=8 loops=1)
Filter: (((link_id)::text = 'SL1714636915'::text) OR ((link_id)::text = 'SL1957747793'::text) OR ((link_id)::text = 'SL1025202362'::text) OR ((link_id)::text = 'SL1303455736'::text) OR ((link_id)::text = 'SL1540383426'::text) OR ((link_id)::text = 'SL1365180540'::text) OR ((link_id)::text = 'SL1967513926'::text) OR ((link_id)::text = 'SL1101513929'::text))
-> Hash (cost=10558.39..10558.39 rows=560339 width=8) (actual time=971.034..971.034 rows=560339 loops=1)
-> Seq Scan on sccp_addr a (cost=0.00..10558.39 rows=560339 width=8) (actual time=0.020..515.698 rows=560339 loops=1)
-> Hash (cost=1.91..1.91 rows=91 width=10) (actual time=8.719..8.719 rows=91 loops=1)
-> Seq Scan on mtp_addr m (cost=0.00..1.91 rows=91 width=10) (actual time=8.556..8.644 rows=91 loops=1)
Total runtime: 106672.004 ms
(записей: 21)


похоже самая долгая операция это -> Seq Scan on sccp_traf_stat s (cost=0.00..858645.88 rows=52454188 width=20) (actual time=0.018..45321.935 rows=52454189 loops=1)!!!! то есть происходит полное сканирование таблицы sccp_traf_stat (52 млн записи)

индексы: таблица
sccp_traf_stat_link_addr_id_idx | sccp_traf_stat
sccp_traf_stat_mtp_addr_id_idx | sccp_traf_stat
sccp_traf_stat_sccp_addr_id_idx | sccp_traf_stat
sccp_traf_stat_time_id_idx | sccp_traf_stat

time_intervals_css7_btime_idx | time_intervals_css7
time_intervals_css7_etime_idx | time_intervals_css7
time_intervals_css7_pk | time_intervals_css7

mtp_addr_id_idx | mtp_addr
mtp_addr_pk | mtp_addr

sccp_addr_pk | sccp_addr

link_addr_pk | link_addr

если установить значение set enable_seqscan to off;

------------------------------------------- QUERY PLAN---------------------------------------
Hash Join (cost=30453.96..3055908.69 rows=2151318 width=45) (actual time=2732.725..81369.601 rows=1800406 loops=1)
Hash Cond: ("outer".mtp_addr_id = "inner".mtp_addr_id)
-> Hash Join (cost=30449.73..3023634.69 rows=2151318 width=43) (actual time=2700.365..78918.081 rows=1800406 loops=1)
Hash Cond: ("outer".sccp_addr_id = "inner".sccp_addr_id)
-> Hash Join (cost=14237.16..2918736.46 rows=2151318 width=43) (actual time=223.845..72404.405 rows=1800406 loops=1)
Hash Cond: ("outer".time_id = "inner".time_id)
-> Nested Loop (cost=14213.36..2831631.76 rows=13113547 width=31) (actual time=194.498..68848.713 rows=4315428 loops=1)
-> Index Scan using link_addr_pk on link_addr l (cost=0.00..3.87 rows=7 width=19) (actual time=8.270..8.365 rows=8 loops=1)
Filter: (((link_id)::text = 'SL1714636915'::text) OR ((link_id)::text = 'SL1957747793'::text) OR ((link_id)::text = 'SL1025202362'::text) OR ((link_id)::text = 'SL1303455736'::text) OR ((link_id)::text = 'SL1540383426'::text) OR ((link_id)::text = 'SL1365180540'::text) OR ((link_id)::text = 'SL1967513926'::text) OR ((link_id)::text = 'SL1101513929'::text))
-> Bitmap Heap Scan on sccp_traf_stat s (cost=14213.36..373295.54 rows=2497818 width=20) (actual time=237.089..8132.068 rows=539428 loops=8)
Recheck Cond: ("outer".link_addr_id = s.link_addr_id)
-> Bitmap Index Scan on sccp_traf_stat_link_addr_id_idx (cost=0.00..14213.36 rows=2497818 width=0) (actual time=229.089..229.090 rows=539428 loops=8)
Index Cond: ("outer".link_addr_id = s.link_addr_id)
-> Hash (cost=23.11..23.11 rows=272 width=20) (actual time=29.317..29.317 rows=672 loops=1)
-> Index Scan using time_intervals_css7_etime_idx on time_intervals_css7 t (cost=0.00..23.11 rows=272 width=20) (actual time=19.379..28.775 rows=672 loops=1)
Index Cond: ((etime >= '2007-08-01 00:00:00+04'::timestamp with time zone) AND (etime <= '2007-08-15 00:00:00+04'::timestamp with time zone))
Filter: ((btime >= '2007-08-01 00:00:00+04'::timestamp with time zone) AND (btime <= '2007-08-15 00:00:00+04'::timestamp with time zone))
-> Hash (cost=12348.73..12348.73 rows=560339 width=8) (actual time=2476.346..2476.346 rows=560339 loops=1)
-> Index Scan using sccp_addr_pk on sccp_addr a (cost=0.00..12348.73 rows=560339 width=8) (actual time=17.276..2007.011 rows=560339 loops=1)
-> Hash (cost=4.00..4.00 rows=91 width=10) (actual time=32.350..32.350 rows=91 loops=1)
-> Index Scan using mtp_addr_pk on mtp_addr m (cost=0.00..4.00 rows=91 width=10) (actual time=32.175..32.274 rows=91 loops=1)
Total runtime: 81947.564 ms
(записей: 22)


Bitmap Heap Scan on sccp_traf_stat s (cost=14213.36..373295.54 rows=2497818 width=20) (actual time=237.089..8132.068 rows=539428 loops=8)!!!

мало что меняется в плане скорости выполнения. не подскажите что можно сделать? может как-то перестроить запрос, ввести какие-то индексы или перестроить таблицу?
возможно выводить сразу по 100 строк, необязательно все. все равно делать paging (offset/limit 100)
ожидать больше минуты это слишком много

если что-то непонятно, нужно уточнить структуру таблиц спрашивайте
заранее спасибо
------------------------------------------------------------------------------------------------------
тестовая конфигурация: intel E6300, 1Gb RAM, OS Debian, postgresql 8.1.9

LeXa NalBatприсоединяюсь к советам Andrey Daeron "VACUUM FULL ANALYZE" и "% статистики поднять"

VACUUM FULL ANALYZE уже проведен, default_statistics_target = 1000 ( в postgresql.conf, если вы об этом)

LeXa NalBat pjatachokперестроить таблицу?
то есть изменить структуру базы?

Ну да, разбить таблицу на несколько связаных таблиц как-то(разделенные таблицы по time_id)
если так то прошу давать предложения
CREATE TABLE sccp_traf_stat( count INTEGER NOT NULL time_id INTEGER mtp_addr_id INTEGER sccp_addr_id INTEGER link_addr_id INTEGER CONSTRAINT sccp_traf_time_fk FOREIGN KEY ( time_id ) REFERENCES time_intervals_css7 ( time_id ) ON DELETE CASCADE CONSTRAINT sccp_traf_mtp_addr_fk FOREIGN KEY( mtp_addr_id ) REFERENCES mtp_addr ( mtp_addr_id ) CONSTRAINT sccp_traf_addr_fk FOREIGN KEY( sccp_addr_id ) REFERENCES sccp_addr ( sccp_addr_id ) CONSTRAINT sccp_traf_link_addr_fk FOREIGN KEY( link_addr_id ) REFERENCES link_addr ( link_addr_id ));CREATE INDEX sccp_traf_stat_link_addr_id_idx on sccp_traf_stat(link_addr_id);CREATE INDEX sccp_traf_stat_time_id_idx on sccp_traf_stat(time_id);CREATE INDEX sccp_traf_stat_sccp_addr_id_idx on sccp_traf_stat(sccp_addr_id);CREATE INDEX sccp_traf_stat_mtp_addr_id_idx on sccp_traf_stat(mtp_addr_id);


LeXa NalBat
pjatachokвозможно выводить сразу по 100 строк, необязательно все. все равно делать paging (offset/limit 100)
в каком порядке выводить?
это может сильно ускорить...

например в порядке возростания/убывания t.btime

LeXa NalBat
PS Andrey DaeronДа и запросы у Вас разное кол-во строк вернули.вроде бы одинаковое (actual rows=1800406)
да, одинаковое - 1800406
хотя конечно выводить столько строк бессмысленно => достаточно выводить по 100 - 1000 (offset/limit)

спасибо за проявленный интерес

LeXa NalBat ОГРОМНОЕ спасибо, offset/analyse помог и теперь запрос выполняется 600мс для 100 строк
(ой я это уже пробывал это решение раньше но забыл его записать, ато все в голове paging вертелся)

осталась еще одна последняя проблема -
запрос вида


SELECT t.time_id, t.btime, t.etime, l.link_id, m.ni, m.opc, m.dpc, sum( i.count)
FROM sccp_traf_stat as i, time_intervals_css7 AS t, link_addr AS l, mtp_addr AS m
WHERE i.time_id = t.time_id AND i.link_addr_id = l.link_addr_id and i.mtp_addr_id = m.mtp_addr_id
AND ( t.btime BETWEEN '2007-7-1 0:0:0' AND '2007-8-30 0:0:0' )
AND ( t.etime BETWEEN '2007-7-1 0:0:0' AND '2007-8-30 0:0:0' )
AND l.link_id IN ('SL1681692777','SL846930886','SL1649760492','SL596516649')
GROUP BY t.time_id, t.btime, t.etime, l.link_id, m.ni, m.opc, m.dpc;


выполняется также безумно долго - 60036.627 мс
-----------------------------Query plan-------------------
HashAggregate (cost=1578733.83..1582267.03 rows=282656 width=45) (actual time=102254.685..102267.556 rows=9889 loops=1) -> Hash Join (cost=51.88..1433565.09 rows=7258437 width=45) (actual time=100.871..90317.531 rows=5577760 loops=1) Hash Cond: ("outer".mtp_addr_id = "inner".mtp_addr_id) -> Hash Join (cost=49.75..1324686.40 rows=7258437 width=43) (actual time=85.341..82911.008 rows=5577760 loops=1) Hash Cond: ("outer".time_id = "inner".time_id) -> Hash Join (cost=1.57..1195852.94 rows=7493455 width=27) (actual time=71.840..76005.453 rows=5646712 loops=1) Hash Cond: ("outer".link_addr_id = "inner".link_addr_id) -> Seq Scan on sccp_traf_stat i (cost=0.00..858645.88 rows=52454188 width=16) (actual time=7.813..45753.521 rows=52454189 loops=1) -> Hash (cost=1.56..1.56 rows=4 width=19) (actual time=9.778..9.778 rows=4 loops=1) -> Seq Scan on link_addr l (cost=0.00..1.56 rows=4 width=19) (actual time=9.740..9.768 rows=4 loops=1) Filter: (((link_id)::text = 'SL1681692777'::text) OR ((link_id)::text = 'SL846930886'::text) OR ((link_id)::text = 'SL1649760492'::text) OR ((link_id)::text = 'SL596516649'::text)) -> Hash (cost=44.16..44.16 rows=1606 width=20) (actual time=13.474..13.474 rows=1631 loops=1) -> Seq Scan on time_intervals_css7 t (cost=0.00..44.16 rows=1606 width=20) (actual time=9.360..12.517 rows=1631 loops=1) Filter: ((btime >= '2007-07-01 00:00:00+04'::timestamp with time zone) AND (btime <= '2007-08-30 00:00:00+04'::timestamp with time zone) AND (etime >= '2007-07-01 00:00:00+04'::timestamp with time zone) AND (etime <= '2007-08-30 00:00:00+04'::timestamp with time zone)) -> Hash (cost=1.91..1.91 rows=91 width=10) (actual time=15.502..15.502 rows=91 loops=1) -> Seq Scan on mtp_addr m (cost=0.00..1.91 rows=91 width=10) (actual time=15.318..15.411 rows=91 loops=1) Total runtime: 102272.382 ms(записей: 17)

опять же, set enable_sqcscan to off дает:
HashAggregate (cost=2000967.31..2004500.51 rows=282656 width=45) (actual time=90066.645..90078.649 rows=9889 loops=1) -> Hash Join (cost=14273.43..1855798.57 rows=7258437 width=45) (actual time=1375.860..77675.752 rows=5577760 loops=1) Hash Cond: ("outer".mtp_addr_id = "inner".mtp_addr_id) -> Hash Join (cost=14269.20..1746917.78 rows=7258437 width=43) (actual time=1352.190..70381.605 rows=5577760 loops=1) Hash Cond: ("outer".time_id = "inner".time_id) -> Nested Loop (cost=14213.36..1618076.67 rows=7493455 width=27) (actual time=1338.557..63290.221 rows=5646712 loops=1) -> Index Scan using link_addr_pk on link_addr l (cost=0.00..3.59 rows=4 width=19) (actual time=4.239..4.300 rows=4 loops=1) Filter: (((link_id)::text = 'SL1681692777'::text) OR ((link_id)::text = 'SL846930886'::text) OR ((link_id)::text = 'SL1649760492'::text) OR ((link_id)::text = 'SL596516649'::text)) -> Bitmap Heap Scan on sccp_traf_stat i (cost=14213.36..373295.54 rows=2497818 width=16) (actual time=589.663..14594.895 rows=1411678 loops=4) Recheck Cond: (i.link_addr_id = "outer".link_addr_id) -> Bitmap Index Scan on sccp_traf_stat_link_addr_id_idx (cost=0.00..14213.36 rows=2497818 width=0) (actual time=519.607..519.607 rows=1411678 loops=4) Index Cond: (i.link_addr_id = "outer".link_addr_id) -> Hash (cost=51.82..51.82 rows=1606 width=20) (actual time=13.607..13.607 rows=1631 loops=1) -> Index Scan using time_intervals_css7_pk on time_intervals_css7 t (cost=0.00..51.82 rows=1606 width=20) (actual time=8.876..12.553 rows=1631 loops=1) Filter: ((btime >= '2007-07-01 00:00:00+04'::timestamp with time zone) AND (btime <= '2007-08-30 00:00:00+04'::timestamp with time zone) AND (etime >= '2007-07-01 00:00:00+04'::timestamp with time zone) AND (etime <= '2007-08-30 00:00:00+04'::timestamp with time zone)) -> Hash (cost=4.00..4.00 rows=91 width=10) (actual time=23.643..23.643 rows=91 loops=1) -> Index Scan using mtp_addr_pk on mtp_addr m (cost=0.00..4.00 rows=91 width=10) (actual time=23.465..23.562 rows=91 loops=1) Total runtime: 90083.420 ms
что быстрее в среднем на 10сек

LIMIT 100; не помогает(видимо из-за sum( i.count) и GROUP BY ... происходит опять же просмотр всей таблицы):
Limit (cost=2000967.31..2000968.56 rows=100 width=45) (actual time=87099.687..87099.883 rows=100 loops=1) -> HashAggregate (cost=2000967.31..2004500.51 rows=282656 width=45) (actual time=87099.684..87099.824 rows=100 loops=1) -> Hash Join (cost=14273.43..1855798.57 rows=7258437 width=45) (actual time=1033.232..74931.258 rows=5577760 loops=1) Hash Cond: ("outer".mtp_addr_id = "inner".mtp_addr_id) -> Hash Join (cost=14269.20..1746917.78 rows=7258437 width=43) (actual time=1018.446..67643.697 rows=5577760 loops=1) Hash Cond: ("outer".time_id = "inner".time_id) -> Nested Loop (cost=14213.36..1618076.67 rows=7493455 width=27) (actual time=994.215..60539.688 rows=5646712 loops=1) -> Index Scan using link_addr_pk on link_addr l (cost=0.00..3.59 rows=4 width=19) (actual time=0.217..0.274 rows=4 loops=1) Filter: (((link_id)::text = 'SL1681692777'::text) OR ((link_id)::text = 'SL846930886'::text) OR ((link_id)::text = 'SL1649760492'::text) OR ((link_id)::text = 'SL596516649'::text)) -> Bitmap Heap Scan on sccp_traf_stat i (cost=14213.36..373295.54 rows=2497818 width=16) (actual time=525.474..13936.724 rows=1411678 loops=4) Recheck Cond: (i.link_addr_id = "outer".link_addr_id) -> Bitmap Index Scan on sccp_traf_stat_link_addr_id_idx (cost=0.00..14213.36 rows=2497818 width=0) (actual time=511.471..511.471 rows=1411678 loops=4) Index Cond: (i.link_addr_id = "outer".link_addr_id) -> Hash (cost=51.82..51.82 rows=1606 width=20) (actual time=24.206..24.206 rows=1631 loops=1) -> Index Scan using time_intervals_css7_pk on time_intervals_css7 t (cost=0.00..51.82 rows=1606 width=20) (actual time=8.842..23.161 rows=1631 loops=1) Filter: ((btime >= '2007-07-01 00:00:00+04'::timestamp with time zone) AND (btime <= '2007-08-30 00:00:00+04'::timestamp with time zone) AND (etime >= '2007-07-01 00:00:00+04'::timestamp with time zone) AND (etime <= '2007-08-30 00:00:00+04'::timestamp with time zone)) -> Hash (cost=4.00..4.00 rows=91 width=10) (actual time=14.762..14.762 rows=91 loops=1) -> Index Scan using mtp_addr_pk on mtp_addr m (cost=0.00..4.00 rows=91 width=10) (actual time=14.443..14.563 rows=91 loops=1) Total runtime: 87102.545 ms(записей: 19)
LIMIT применяется уже к конечным результатам. Bitmap Heap Scan on sccp_traf_stat i (cost=14213.36..373295.54 rows=2497818 width=16) (actual time=525.474..13936.724 rows=1411678 loops=4)

отключение Bitmap Heap Scan приводит к
Limit (cost=35716833.32..35716834.57 rows=100 width=45) (actual time=98085.182..98085.381 rows=100 loops=1) -> HashAggregate (cost=35716833.32..35720366.52 rows=282656 width=45) (actual time=98085.179..98085.319 rows=100 loops=1) -> Hash Join (cost=60.06..35571664.58 rows=7258437 width=45) (actual time=69.346..85325.954 rows=5577760 loops=1) Hash Cond: ("outer".mtp_addr_id = "inner".mtp_addr_id) -> Hash Join (cost=55.83..35462783.80 rows=7258437 width=43) (actual time=42.741..77613.768 rows=5577760 loops=1) Hash Cond: ("outer".time_id = "inner".time_id) -> Nested Loop (cost=0.00..35333942.68 rows=7493455 width=27) (actual time=27.108..70262.870 rows=5646712 loops=1) -> Index Scan using link_addr_pk on link_addr l (cost=0.00..3.59 rows=4 width=19) (actual time=0.236..0.283 rows=4 loops=1) Filter: (((link_id)::text = 'SL1681692777'::text) OR ((link_id)::text = 'SL846930886'::text) OR ((link_id)::text = 'SL1649760492'::text) OR ((link_id)::text = 'SL596516649'::text)) -> Index Scan using sccp_traf_stat_link_addr_id_idx on sccp_traf_stat i (cost=0.00..8802262.05 rows=2497818 width=16) (actual time=45.118..16333.448 rows=1411678 loops=4) Index Cond: (i.link_addr_id = "outer".link_addr_id) -> Hash (cost=51.82..51.82 rows=1606 width=20) (actual time=15.614..15.614 rows=1631 loops=1) -> Index Scan using time_intervals_css7_pk on time_intervals_css7 t (cost=0.00..51.82 rows=1606 width=20) (actual time=10.778..14.534 rows=1631 loops=1) Filter: ((btime >= '2007-07-01 00:00:00+04'::timestamp with time zone) AND (btime <= '2007-08-30 00:00:00+04'::timestamp with time zone) AND (etime >= '2007-07-01 00:00:00+04'::timestamp with time zone) AND (etime <= '2007-08-30 00:00:00+04'::timestamp with time zone)) -> Hash (cost=4.00..4.00 rows=91 width=10) (actual time=26.582..26.582 rows=91 loops=1) -> Index Scan using mtp_addr_pk on mtp_addr m (cost=0.00..4.00 rows=91 width=10) (actual time=26.379..26.498 rows=91 loops=1) Total runtime: 98091.415 ms

проблема опять в этой огромной таблице sccp_traf_stat(52 млн записей 2610Мб, размер индексов - 3589Мб!!!!!!)
перестраивать базу в принципе можно как душе угодно - мне просто дали время и сказали - "просто сделай чтобы это работало быстро"
желательно конечно ограничиться перестройкой лишь таблицы sccp_traf_stat, введением дополнительных

в WHERE могут меняться абсолютно все параметры(запрос формируется динамически в GUI клиенте)
WHERE i.time_id = t.time_id
AND ( t.btime BETWEEN '2007-8-1 0:0:0' AND '2007-8-30 0:0:0' )
AND ( t.etime BETWEEN '2007-8-1 0:0:0' AND '2007-8-30 0:0:0' )
AND i.link_addr_id = l.link_addr_id and i.mtp_addr_id = m.mtp_addr_id
AND l.link_id IN('SL1681692777','SL846930886','SL1714636915','SL1957747793','SL304089172','SL424238335','SL719885386')

LeXa NalBat, большое спасибо, сейчас проверю как создание доптаблицы повлияет...
а есть ли возможность как-то уменьшить размер индексов для таблицы sccp_traf_stat ?
3.6Гб как-то смущают
разве что еще попробую удалить неиспользуемые индексы (статистика)

спасибо

LeXa NalBat, огромное спасибо, введение доптаблицы помогло сократить время выполнения запроса до 0,6 сек что есть отличный результат! а можно что-то подобное сделать с запросом:

SELECT l.link_id, s.cgssn, s.cdssn, i.mtype, m.ni, m.opc, m.dpc, SUM(i.count)
FROM sccp_msg_stat AS i, mtp_addr AS m, link_addr AS l, time_intervals_css7 AS t, sccp_addr AS s
WHERE t.time_id = i.time_id
AND m.mtp_addr_id = i.mtp_addr_id
AND l.link_addr_id = i.link_addr_id
AND i.sccp_addr_id = s.sccp_addr_id
AND ( t.btime BETWEEN '2007-7-1 0:0:0' AND '2007-10-8 0:0:0' )
AND ( t.etime BETWEEN '2007-7-1 0:0:0' AND '2007-10-8 0:0:0' )
AND link_id IN ( 'SL1804289383', 'SL336465782', 'SL35005211', 'SL521595368' )
GROUP BY i.mtype, l.link_id, s.cgssn, s.cdssn, m.ni,m.opc,m.dpc;


?

в sccp_msg_stat тоже 52 млн строк и запрос выполняется 300сек

LeXa NalBat, пытался:


CREATE TABLE t2 as
SELECT time_id, mtp_addr_id, link_addr_id, sccp_addr_id, mtype, SUM(count) as cnt
FROM sccp_msg_stat
GROUP BY time_id, mtp_addr_id, link_addr_id, sccp_addr_id, mtype;

SELECT l.link_id, s.cgssn, s.cdssn, t2.mtype, m.ni, m.opc, m.dpc, SUM(t2.count)
FROM t2, mtp_addr AS m, link_addr AS l, time_intervals_css7 AS t, sccp_addr AS s
WHERE t.time_id = t2.time_id
AND m.mtp_addr_id = t2.mtp_addr_id
AND l.link_addr_id = t2.link_addr_id
AND s.sccp_addr_id = t2.sccp_addr_id
AND ( t.btime BETWEEN '2007-7-1 0:0:0' AND '2007-10-8 0:0:0' )
AND ( t.etime BETWEEN '2007-7-1 0:0:0' AND '2007-10-8 0:0:0' )
AND link_id IN ( 'SL1804289383', 'SL336465782', 'SL35005211', 'SL521595368' ) ;



проблема в том, что

SELECT count(A.time_id), count(B.time_id)
FROM t2 as A, sccp_msg_stat as B;

52469125 | 52469125

то есть t2 ==sccp_msg_stat :)

так что такое решение не покатит :)
нужно как можно скорее на "ранних этапах" обработки запроса "отсекать" в sccp_msg_stat "лишние строки"

EXPLAIN
ANALYZE
SELECT l.link_id, s.cgssn, s.cdssn, i.mtype, m.ni, m.opc, m.dpc, SUM(i.count)
FROM sccp_msg_stat AS i, mtp_addr AS m, link_addr AS l, time_intervals_css7 AS t, sccp_addr AS s
WHERE t.time_id = i.time_id
AND m.mtp_addr_id = i.mtp_addr_id
AND l.link_addr_id = i.link_addr_id
AND i.sccp_addr_id = s.sccp_addr_id
AND ( t.btime BETWEEN '2007-7-1 0:0:0' AND '2007-10-8 0:0:0' )
AND ( t.etime BETWEEN '2007-7-1 0:0:0' AND '2007-10-8 0:0:0' )
AND link_id IN ( 'SL1804289383', 'SL336465782', 'SL35005211', 'SL521595368' )
GROUP BY i.mtype, l.link_id, s.cgssn, s.cdssn, m.ni,m.opc,m.dpc;
HashAggregate (cost=1912385.00..1914725.80 rows=187264 width=31) (actual time=312929.834..312930.843 rows=510 loops=1) -> Hash Join (cost=14474.25..1762563.64 rows=7491068 width=31) (actual time=1046.136..263378.764 rows=36614277 loops=1) Hash Cond: ("outer".mtp_addr_id = "inner".mtp_addr_id) -> Hash Join (cost=14472.11..1650195.49 rows=7491068 width=29) (actual time=1041.608..216501.739 rows=36614277 loops=1) Hash Cond: ("outer".sccp_addr_id = "inner".sccp_addr_id) -> Hash Join (cost=49.87..1355027.22 rows=7491068 width=29) (actual time=51.762..146579.267 rows=36614277 loops=1) Hash Cond: ("outer".time_id = "inner".time_id) -> Hash Join (cost=1.57..1223851.32 rows=7495589 width=33) (actual time=22.592..105568.621 rows=36614277 loops=1) Hash Cond: ("outer".link_addr_id = "inner".link_addr_id) -> Seq Scan on sccp_msg_stat i (cost=0.00..886548.24 rows=52469124 width=22) (actual time=2.482..48955.066 rows=52469125 loops=1) -> Hash (cost=1.56..1.56 rows=4 width=19) (actual time=20.090..20.090 rows=4 loops=1) -> Seq Scan on link_addr l (cost=0.00..1.56 rows=4 width=19) (actual time=20.049..20.080 rows=4 loops=1) Filter: (((link_id)::text = 'SL1804289383'::text) OR ((link_id)::text = 'SL336465782'::text) OR ((link_id)::text = 'SL35005211'::text) OR ((link_id)::text = 'SL521595368'::text)) -> Hash (cost=44.16..44.16 rows=1657 width=4) (actual time=29.153..29.153 rows=1658 loops=1) -> Seq Scan on time_intervals_css7 t (cost=0.00..44.16 rows=1657 width=4) (actual time=14.086..27.983 rows=1658 loops=1) Filter: ((btime >= '2007-07-01 00:00:00+04'::timestamp with time zone) AND (btime <= '2007-10-08 00:00:00+04'::timestamp with time zone) AND (etime >= '2007-07-01 00:00:00+04'::timestamp with time zone) AND (etime <= '2007-10-08 00:00:00+04'::timestamp with time zone)) -> Hash (cost=10558.39..10558.39 rows=560339 width=8) (actual time=989.659..989.659 rows=560339 loops=1) -> Seq Scan on sccp_addr s (cost=0.00..10558.39 rows=560339 width=8) (actual time=0.212..561.484 rows=560339 loops=1) -> Hash (cost=1.91..1.91 rows=91 width=10) (actual time=4.470..4.470 rows=91 loops=1) -> Seq Scan on mtp_addr m (cost=0.00..1.91 rows=91 width=10) (actual time=4.297..4.383 rows=91 loops=1) Total runtime: 312966.203 ms(записей: 21)


select count(*) from time_intervals_css7 AS t
where ( t.btime BETWEEN '2007-7-1 0:0:0' AND '2007-10-8 0:0:0' )
and ( t.etime BETWEEN '2007-7-1 0:0:0' AND '2007-10-8 0:0:0' );

1658

select count(*) from sccp_msg_stat AS i, time_intervals_css7 AS t,
where ( t.btime BETWEEN '2007-7-1 0:0:0' AND '2007-10-8 0:0:0' )
and ( t.etime BETWEEN '2007-7-1 0:0:0' AND '2007-10-8 0:0:0' )
and t.time_id = i.time_id;

52469125
в таблице как раз данные с '2007-7-1 0:0:0' по '2007-10-8 0:0:0', вернее по '2007-8-31 0:0:0' :)


select count(*) from link_addr AS l
where link_id IN ( 'SL1804289383', 'SL336465782', 'SL35005211', 'SL521595368' );
4
link_id - уникальное значение,
select count(*) from link_addr; - 28

select count(*) from sccp_msg_stat AS i, link_addr AS l
where link_id IN ( 'SL1804289383', 'SL336465782', 'SL35005211', 'SL521595368' )
and l.link_addr_id = i.link_addr_id;
36614277

LeXa NalBatк вечеру стало хуже соображаться...

приведите еще пожалуйста результаты этих запросов

select count( distinct i.time_id, i.mtp_addr_id, i.link_addr_id, i.sccp_addr_id, i.mtype ) from sccp_msg_stat AS i;
такое не срабатывает, перестроил:

SELECT count(*)
FROM
(
SELECT distinct i.time_id, i.mtp_addr_id, i.link_addr_id, i.sccp_addr_id, i.mtype
from sccp_msg_stat AS i
) as T;
count
----------
52469125
(1 запись)

аам....ну да, все записи различны :(

select count( distinct i.mtp_addr_id, i.link_addr_id, i.sccp_addr_id, i.mtype ) from sccp_msg_stat AS i;
SELECT count(*)
FROM
(
SELECT distinct i.mtp_addr_id, i.link_addr_id, i.sccp_addr_id, i.mtype
from sccp_msg_stat AS i
) as T;
count
---------
1056313
(1 запись)

vois-la

select count(*) from ( select distinct m.mtp_addr_id from mtp_addr AS m ) as t1;
select count(*) from ( select distinct m.mtp_addr_id from mtp_addr AS m ) as t1;
count
-------
91
(1 запись)

select count(*) from ( select distinct m.ni,m.opc,m.dpc from mtp_addr AS m ) as t1;
select count(*) from ( select distinct m.ni,m.opc,m.dpc from mtp_addr AS m ) as t1;
count
-------
91
(1 запись)

select count(*) from ( select distinct s.sccp_addr_id from sccp_addr AS s ) as t1;
select count(*) from ( select distinct s.sccp_addr_id from sccp_addr AS s ) as t1;
count
--------
560339
(1 запись)

select count(*) from ( select distinct s.cgssn, s.cdssn from sccp_addr AS s ) as t1;
select count(*) from ( select distinct s.cgssn, s.cdssn from sccp_addr AS s ) as t1;
count
-------
54
(1 запись)

LeXa NalBat,
не могли бы Вы обьяснить, что Вы имеете ввиду?
не это ли:

CREATE TABLE t2 AS
SELECT i.time_id as T2time_id, i.mtp_addr_id as T2mtp_addr_id, i.link_addr_id as T2link_addr_id, i.mtype as T2mtype, s.sccp_addr_id as T2sccp_addr_id, s.cgssn as T2cgssn, s.cdssn as T2cdssn, SUM(i.count) as T2cnt
FROM sccp_addr as s, sccp_msg_stat as i
WHERE s.sccp_addr_id = i.sccp_addr_id
GROUP BY T2time_id, T2mtp_addr_id, T2link_addr_id, T2mtype, T2sccp_addr_id, T2cgssn, T2cdssn ;

SELECT l.link_id, T2.T2cgssn, T2.T2cdssn, T2.mtype, m.ni, m.opc, m.dpc, SUM(T2.T2cnt)
FROM T2, mtp_addr AS m, link_addr AS l, time_intervals_css7 AS t
WHERE t.time_id = T2.T2time_id
AND m.mtp_addr_id = T2.T2mtp_addr_id
AND l.link_addr_id = T2.T2link_addr_id
AND ( t.btime BETWEEN '2007-7-1 0:0:0' AND '2007-10-8 0:0:0' )
AND ( t.etime BETWEEN '2007-7-1 0:0:0' AND '2007-10-8 0:0:0' )
AND l.link_id IN ( 'SL1804289383', 'SL336465782', 'SL35005211', 'SL521595368' );

?

не, какую-то я ерунду написал :

select count(*) from t2;
52469124

ой, забыл в запросе написать GROUP BY l.link_id, T2.T2cgssn, T2.T2cdssn, T2.mtype, m.ni, m.opc, m.dpc;
:)

LeXa NalBat, ОГРОМНОЕ СПАСИБО))))) теперь выполняется за 1.5 сек!!!!! )))) осталось почитать материал по составлению триггеров


ЗЫ думаю мне пора проставляться))) жаль не в москве живу

Andrey Daeron, спасибо и Вам за проявленный интерес к проблеме