помогите оптимизировать запрос, пожалуйста / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / помогите оптимизировать запрос, пожалуйста

57 сообщений из 57, показаны все 3 страниц

все

помогите оптимизировать запрос, пожалуйста

#34847450

pjatachok

Гость

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

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

...

Рейтинг:

0 / 0

04.10.2007, 16:52

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34847613

Andrey Daeron

Участник

Откуда: Киев

Сообщения: 1 036

Рейтинг: 0 / 0

VACUUM FULL ANALYZE?

Оптимизатор нехило ошибается при оценке кол-ва строк:
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)

Может % статистики поднять?

Да и запросы у Вас разное кол-во строк вернули.

ЗЫ И лучше план обрамлять в теги SRC тогда они более читаемы.

...

Рейтинг:

0 / 0

04.10.2007, 17:35

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34848977

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

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

наверное быстрее будет план

Код: plaintext

1.
2.
3.
4.
5.
6.

...
  hashjoin (actual rows = 1 800 406)
    hashjoin (actual rows = 4 315 428)
      seqscan s (actual rows = 52 454 189)
      hash l
    hash t
...

можно попробовать его добиться с помощью explicit joins
но все равно это будет работать не менее 45 секунд (secscan s)

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

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

PS Andrey DaeronДа и запросы у Вас разное кол-во строк вернули.вроде бы одинаковое (actual rows=1800406)

...

Рейтинг:

0 / 0

05.10.2007, 11:25

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34849226

pjatachok

Гость

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)

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

...

Рейтинг:

0 / 0

05.10.2007, 12:18

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34849353

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

pjatachokвозможно выводить сразу по 100 строк, необязательно все
например в порядке возростания/убывания t.btimeпри наличии индексов
t (btime)
s (time_id)
l (link_id)
m (mtp_addr_id)
a (sccp_addr_id)
попробуйте запрос "select ... order by t.btime limit 100 offset 100", покажите explain analyze

pjatachok LeXa NalBatто есть изменить структуру базы?Ну да, разбить таблицу на несколько связаных таблиц как-то(разделенные таблицы по time_id)
если так то прошу давать предложениявсе-таки... насколько серьезно можно изменить структуру БД?

наверное можно придумать такую структуру, например избыточную, OLAP-like, по которой запрос этого вида будет выполняться быстро. (кстати, надо понять, какие параметры в where варьируемые и насколько сильно, а какие постоянные.) но при этом другие выборки и изменение данных будут работать очень медленно.

...

Рейтинг:

0 / 0

05.10.2007, 12:36

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34849717

pjatachok

Гость

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')

...

Рейтинг:

0 / 0

05.10.2007, 13:30

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34850219

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

время от времени перестраивайте или постоянно поддерживайте триггерами дополнительную вспомогательную таблицу

CREATE TABLE t1 AS
SELECT time_id, link_id, mtp_addr_id, sum( i.count) as cnt
FROM sccp_traf_stat
GROUP BY t.time_id, l.link_id, mtp_addr_id;

SELECT t.time_id, t.btime, t.etime, l.link_id, m.ni, m.opc, m.dpc, t1.cnt
FROM t1, time_intervals_css7 AS t, link_addr AS l, mtp_addr AS m
WHERE t1.time_id = t.time_id AND t1.link_addr_id = l.link_addr_id and t1.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');

...

Рейтинг:

0 / 0

05.10.2007, 15:13

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34850300

pjatachok

Гость

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

спасибо

...

Рейтинг:

0 / 0

05.10.2007, 15:29

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34850365

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

pjatachokа есть ли возможность как-то уменьшить размер индексов для таблицы sccp_traf_stat ?удлаить неиспользуемые, reindex, пересоздать

pjatachok3.6Гб как-то смущают50 миллионов строк тоже ... :)

...

Рейтинг:

0 / 0

05.10.2007, 15:45

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34856269

pjatachok

Гость

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;

?

...

Рейтинг:

0 / 0

09.10.2007, 11:58

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34856281

pjatachok

Гость

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

...

Рейтинг:

0 / 0

09.10.2007, 12:00

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34856326

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

Сделать аналогично, с использованием дополнительной таблицы содержащей аггрегированные данные из sccp_msg_stat.

...

Рейтинг:

0 / 0

09.10.2007, 12:09

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34856682

pjatachok

Гость

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 "лишние строки"

...

Рейтинг:

0 / 0

09.10.2007, 13:31

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34857511

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

дайте результаты

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.

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;

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' )
;

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
;

select count(*) from link_addr AS l
  where link_id IN (  'SL1804289383', 'SL336465782', 'SL35005211', 'SL521595368' )
;

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
;

...

Рейтинг:

0 / 0

09.10.2007, 16:23

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34857895

pjatachok

Гость

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

...

Рейтинг:

0 / 0

09.10.2007, 17:40

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34858131

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

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

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

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( distinct i.mtp_addr_id, i.link_addr_id, i.sccp_addr_id, i.mtype ) from sccp_msg_stat AS i;

...

Рейтинг:

0 / 0

09.10.2007, 18:38

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34859577

pjatachok

Гость

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 запись)

...

Рейтинг:

0 / 0

10.10.2007, 11:45

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34859684

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

не понятно, почему получилось

count( distinct i.mtp_addr_id, i.link_addr_id, i.sccp_addr_id, i.mtype )
1056313

GROUP BY i.mtype, l.link_id, s.cgssn, s.cdssn, m.ni,m.opc,m.dpc
actual rows=510

сделайте еще пожалуйста

select count(*) from ( select distinct m.mtp_addr_id 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;

select count(*) from ( select distinct s.sccp_addr_id from sccp_addr AS s ) as t1;
select count(*) from ( select distinct s.cgssn, s.cdssn from sccp_addr AS s ) as t1;

...

Рейтинг:

0 / 0

10.10.2007, 12:05

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34859977

pjatachok

Гость

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 запись)

...

Рейтинг:

0 / 0

10.10.2007, 13:10

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34860053

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

distinct s.sccp_addr_id
560339

distinct s.cgssn, s.cdssn
54

вот эту группировку и надо сделать на предварительном этапе при формированиии дополнительной таблицы

...

Рейтинг:

0 / 0

10.10.2007, 13:26

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34860641

pjatachok

Гость

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' );

?

...

Рейтинг:

0 / 0

10.10.2007, 15:29

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34860690

pjatachok

Гость

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

select count(*) from t2;
52469124

...

Рейтинг:

0 / 0

10.10.2007, 15:38

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34860721

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

почти так. только исключить sccp_addr_id, он ведь не нужен в окончательной группировке.

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.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, 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' );

...

Рейтинг:

0 / 0

10.10.2007, 15:46

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34860828

pjatachok

Гость

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

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

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

...

Рейтинг:

0 / 0

10.10.2007, 16:13

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34860900

pjatachok

Гость

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

...

Рейтинг:

0 / 0

10.10.2007, 16:33

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34860938

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

pjatachokой, забыл в запросе написать GROUP BY l.link_id, T2.T2cgssn, T2.T2cdssn, T2.mtype, m.ni, m.opc, m.dpc;да, точно, ведь требуемая группировка не совпадает с T2. (в первом запросе нужная группировка совпадала с T1)

pjatachokосталось почитать материал по составлению триггеровбудете поддерживать таблицы T1, T2 триггерами? скорость изменения данных конечно упадет. (вопрос(ы) по триггерам задавайте в новой теме:)

pjatachokЗЫ думаю мне пора проставляться))) жаль не в москве живуничего, научно-технический прогресс научится таки когда-нибудь отправлять пиво на мыло :)

...

Рейтинг:

0 / 0

10.10.2007, 16:39

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34875477

pjatachok

Гость

LeXa NalBat(вопрос(ы) по триггерам задавайте в новой теме:)

я все же задам здесь, дабы там не описывать вновь таблицы T1 и T2
LeXa NalBat, может Вы знаете как обойти проблему:
пытаюсь создать триггер для T2:
^{create function updateT2() returns opaque as '

DECLARE

T2record record;

SCCPrecord record;

begin

SELECT cgssn, cdssn INTO SCCPrecord

FROM sccp_addr

WHERE new.sccp_addr_id = sccp_addr.sccp_addr_id;

SELECT * INTO T2record

FROM T2

WHERE T2time_id = new.time_id

AND T2mtp_addr_id = new.mtp_addr_id

AND T2link_addr_id = new.link_addr_id

AND T2mtype = new.mtype

AND T2cgssn = SCCPrecord.cgssn

AND T2cdssn = SCCPrecord.cdssn

if (not found)

then

INSERT INTO T2 (T2time_id, T2mtp_addr_id, T2link_addr_id, T2mtype, T2cgssn, T2cdssn, T2cnt)

VALUES (new.time_id, new.mtp_addr_id, new.link_addr_id, new.mtype, SCCPrecord.cgssn, SCCPrecord.cdssn, new.count);

else

UPDATE T2 SET T2cnt = T2cnt + new.count

WHERE T2time_id = new.time_id

AND T2mtp_addr_id = new.mtp_addr_id

AND T2link_addr_id = new.link_addr_id

AND T2mtype = new.mtype

AND T2cgssn = SCCPrecord.cgssn

AND T2cdssn = SCCPrecord.cdssn

end if;

return NULL;

end;

' language 'plpgsql';

create trigger updateT1 after insert on sccp_msg_stat

for each row execute procedure UpdateT2();}

но проблема в том что
ERROR: INTO specified more than once
CONTEXT: compile of PL/pgSQL function "updatet2" near line 19

можно ли как-то выбрать и сохранить значения cgssn, cdssn без применения INTO? или нужно как-то перестраивать триггер?

...

Рейтинг:

0 / 0

17.10.2007, 15:40

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34875591

4321

Участник

Сообщения: 3 711

Рейтинг: 0 / 0

pjatachok create function updateT2() returns opaque as '
DECLARE
T2record record;
SCCPrecord record;
вы еще забыли тег . попробуйте применить его.

если же серьезно, то скрипт желательно выжелять тегом
[src]
или (если хотите вставить свою подсветку)
[FIX]

...

Рейтинг:

0 / 0

17.10.2007, 16:00

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34875677

pjatachok

Гость

4321 pjatachok create function updateT2() returns opaque as '
DECLARE
T2record record;
SCCPrecord record;
вы еще забыли тег . попробуйте применить его.

если же серьезно, то скрипт желательно выжелять тегом
src
или (если хотите вставить свою подсветку)
FIX

понял, выполнил:

пытаюсь создать триггер для T2:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.

create function updateT2() returns opaque as '
DECLARE
T2record record;
SCCPrecord record;
begin
SELECT cgssn, cdssn INTO SCCPrecord
FROM sccp_addr
WHERE new.sccp_addr_id = sccp_addr.sccp_addr_id;

SELECT * INTO T2record
FROM T2
WHERE T2time_id = new.time_id
AND T2mtp_addr_id = new.mtp_addr_id
AND T2link_addr_id = new.link_addr_id
AND T2mtype = new.mtype
AND T2cgssn = SCCPrecord.cgssn
AND T2cdssn = SCCPrecord.cdssn
if (not found)
then
INSERT INTO T2 (T2time_id, T2mtp_addr_id, T2link_addr_id, T2mtype, T2cgssn, T2cdssn, T2cnt)
VALUES (new.time_id, new.mtp_addr_id, new.link_addr_id, new.mtype, SCCPrecord.cgssn, SCCPrecord.cdssn, new.count);
else
UPDATE T2 SET T2cnt = T2cnt + new.count
WHERE T2time_id = new.time_id
AND T2mtp_addr_id = new.mtp_addr_id
AND T2link_addr_id = new.link_addr_id
AND T2mtype = new.mtype
AND T2cgssn = SCCPrecord.cgssn
AND T2cdssn = SCCPrecord.cdssn
end if;

return NULL;
end;
' language 'plpgsql';

create trigger updateT1 after insert on sccp_msg_stat
for each row execute procedure UpdateT2();

но проблема в том что
ERROR: INTO specified more than once
CONTEXT: compile of PL/pgSQL function "updatet2" near line 19

можно ли как-то выбрать и сохранить значения cgssn, cdssn без применения INTO? или нужно как-то перестраивать триггер?

...

Рейтинг:

0 / 0

17.10.2007, 16:17

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34875708

4321

Участник

Сообщения: 3 711

Рейтинг: 0 / 0

"не вникая какой в том прок, что за том в руках и о чем глава" (с)
pjatachok 4321 pjatachok create function updateT2() returns opaque as '
DECLARE
T2record record;
SCCPrecord record;
вы еще забыли тег . попробуйте применить его.

если же серьезно, то скрипт желательно выжелять тегом
src
или (если хотите вставить свою подсветку)
FIX

понял, выполнил:

пытаюсь создать триггер для T2:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.

create function updateT2() returns opaque as '
DECLARE
T2record record;
SCCPrecord record;
begin
SELECT cgssn, cdssn INTO SCCPrecord
FROM sccp_addr
WHERE new.sccp_addr_id = sccp_addr.sccp_addr_id;

SELECT * INTO T2record
FROM T2
WHERE T2time_id = new.time_id
AND T2mtp_addr_id = new.mtp_addr_id
AND T2link_addr_id = new.link_addr_id
AND T2mtype = new.mtype
AND T2cgssn = SCCPrecord.cgssn
AND T2cdssn = SCCPrecord.cdssn
-- А КТО SELECT будет завершать!!! ;;;;;;
if (not found)
then
INSERT INTO T2 (T2time_id, T2mtp_addr_id, T2link_addr_id, T2mtype, T2cgssn, T2cdssn, T2cnt)
VALUES (new.time_id, new.mtp_addr_id, new.link_addr_id, new.mtype, SCCPrecord.cgssn, SCCPrecord.cdssn, new.count);
else
UPDATE T2 SET T2cnt = T2cnt + new.count
WHERE T2time_id = new.time_id
AND T2mtp_addr_id = new.mtp_addr_id
AND T2link_addr_id = new.link_addr_id
AND T2mtype = new.mtype
AND T2cgssn = SCCPrecord.cgssn
AND T2cdssn = SCCPrecord.cdssn
end if;

return NULL;
end;
' language 'plpgsql';

create trigger updateT1 after insert on sccp_msg_stat
for each row execute procedure UpdateT2();

...

Рейтинг:

0 / 0

17.10.2007, 16:25

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34875721

4321

Участник

Сообщения: 3 711

Рейтинг: 0 / 0

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.

create function updateT2() returns opaque as $$
DECLARE
T2record record;
SCCPrecord record;
begin
SELECT cgssn, cdssn INTO SCCPrecord
FROM sccp_addr
WHERE new.sccp_addr_id = sccp_addr.sccp_addr_id;

SELECT * INTO T2record
FROM T2
WHERE T2time_id = new.time_id
AND T2mtp_addr_id = new.mtp_addr_id
AND T2link_addr_id = new.link_addr_id
AND T2mtype = new.mtype
AND T2cgssn = SCCPrecord.cgssn
AND T2cdssn = SCCPrecord.cdssn
-- А КТО SELECT будет завершать!!! ;;;;;;
if (not found)
then
INSERT INTO T2 (T2time_id, T2mtp_addr_id, T2link_addr_id, T2mtype, T2cgssn, T2cdssn, T2cnt)
VALUES (new.time_id, new.mtp_addr_id, new.link_addr_id, new.mtype, SCCPrecord.cgssn, SCCPrecord.cdssn, new.count);
else
UPDATE T2 SET T2cnt = T2cnt + new.count
WHERE T2time_id = new.time_id
AND T2mtp_addr_id = new.mtp_addr_id
AND T2link_addr_id = new.link_addr_id
AND T2mtype = new.mtype
AND T2cgssn = SCCPrecord.cgssn
AND T2cdssn = SCCPrecord.cdssn
end if;

return NULL;
end;
$$ language 'plpgsql';

create trigger updateT1 after insert on sccp_msg_stat
for each row execute procedure UpdateT2();

...

Рейтинг:

0 / 0

17.10.2007, 16:27

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34876029

pjatachok

Гость

ооооо!!!!! 4321 , дико извиняюсь, забыл в конце селекта поставить ; )))))))
вопрос исчерпан

...

Рейтинг:

0 / 0

17.10.2007, 17:53

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34879057

pjatachok

Гость

LeXa NalBat,
только что выяснилось что вместо запроса

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.

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;

будеьт стоять запрос

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

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' )

AND s.cgssn= 4  AND s.cdssn= 6  AND s.cgpa LIKE '????????' AND s.cdpa LIKE '????????'  //новое условие

GROUP BY i.mtype, l.link_id, s.cgssn, s.cdssn, m.ni,m.opc,m.dpc;

и в данном случае имея таблицу T2 ничего с s.cgpa LIKE '????????' AND s.cdpa LIKE '????????' похоже не сделать => таблица T2 в таком виде не годится(((( вместо '????????' будут стоять произвольные регулярные выражения....и что же делать?

...

Рейтинг:

0 / 0

18.10.2007, 18:34

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34879321

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

pjatachok

Код: plaintext

1.
2.

SELECT ... s.cgssn, s.cdssn ...
WHERE ... s.cgssn= 4  AND s.cdssn= 6  ...
GROUP BY ... s.cgssn, s.cdssn ...

видимо s.cgssn, s.cdssn в SELECT и GROUP BY лишние. но на скорость это не влияет.

автор

Код: plaintext

1.
2.
3.
4.

distinct s.sccp_addr_id
 560339 

distinct s.cgssn, s.cdssn
 54

чему равно distinct s.cgssn, s.cdssn, s.cgpa, s.cdpa?

если намного меньше, чем 560339, то можно добавить s.cgpa, s.cdpa в предварительную группировку при формировании таблицы T2.

...

Рейтинг:

0 / 0

18.10.2007, 21:23

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34880209

pjatachok

Гость

AND s.cgssn=4 AND s.cdssn=6 AND s.cgpa LIKE '????????' AND s.cdpa LIKE '????????' - это опция, иногда это есть в запросе, иногда нет

LeXa NalBat чему равно distinct s.cgssn, s.cdssn, s.cgpa, s.cdpa?

Код: plaintext

1.
2.
3.
4.

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

и кстати

Код: plaintext

1.
2.
3.
4.

select count(*) from sccp_addr;
 count
--------
  560340 
( 1  запись)

что логично так как эти 4 поля только и входят в sccp_addr кроме sccp_addr_id

других способов сокращения времени выборки нет?

...

Рейтинг:

0 / 0

19.10.2007, 11:24

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34880561

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

покажите explain analyze ... where ... s.cgpa LIKE '?1?' AND s.cdpa LIKE '?2?' ...

какая избирательность (селктивность) по условию s.cgpa LIKE '?1?' AND s.cdpa LIKE '?2?'? то есть чему равно select count(*) from sccp_addr AS s where s.cgpa LIKE '?1?' AND s.cdpa LIKE '?2?'.

параметры '?1?' и '?2?' сильно варьируемы, или например могут принимать несколько значений? они могут начинаться с '_' или '%'?

...

Рейтинг:

0 / 0

19.10.2007, 12:38

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34881216

pjatachok

Гость

LeXa NalBat, вместо любого знака вопроса в '???????' может быть любая цифра, то есть это будут значения вида
'_23_45__' (например, где _ - любой символ-цифра (regexp в postgresql))

...

Рейтинг:

0 / 0

19.10.2007, 14:56

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34885015

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

pjatachokвместо любого знака вопроса в '???????' может быть любая цифра, то есть это будут значения вида
'_23_45__' (например, где _ - любой символ-цифра (regexp в postgresql))я правильно понял?
значения параметров в выражениях LIKE состоят из цифр и подчеркиваний, причем могут начинаться с подчеркивания?
например:
1234567
12__34_
__1__2_

explain analyze и count(*) покажите?

...

Рейтинг:

0 / 0

22.10.2007, 14:43

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34885365

pjatachok

Гость

автор
я правильно понял?
значения параметров в выражениях LIKE состоят из цифр и подчеркиваний, причем могут начинаться с подчеркивания?

LeXa NalBat , правильно

EXPLAIN ANALYSE SELECT m.ni, m.opc, m.dpc, l.link_id, s.cgssn, s.cdssn, i.mtype, 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-8-31 0:0:0' )
AND ( t.etime BETWEEN '2007-7-1 0:0:0' AND '2007-8-31 0:0:0' )
AND link_id IN ( 'SL521595368', 'SL336465782', 'SL1804289383', 'SL35005211' )
AND s.cgssn=6 AND s.cdssn=7 AND s.cgpa LIKE '7:99______' AND s.cdpa LIKE '1:79______'
GROUP BY m.ni,m.opc,m.dpc, l.link_id, s.cgssn, s.cdssn, i.mtype;

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

HashAggregate  (cost= 1165102 . 03 .. 1165102 . 04  rows= 1  width= 31 ) (actual time= 77560 . 080 .. 77560 . 095  rows= 14  loops= 1 )
   ->  Nested Loop  (cost= 16165 . 49 .. 1165101 . 77  rows= 13  width= 31 ) (actual time= 1077 . 697 .. 77559 . 041  rows= 197  loops= 1 )
         ->  Hash Join  (cost= 16165 . 49 .. 1165062 . 30  rows= 13  width= 35 ) (actual time= 1065 . 260 .. 77348 . 703  rows= 197  loops= 1 )
               Hash Cond: ("outer".link_addr_id = "inner".link_addr_id)
               ->  Hash Join  (cost= 16163 . 92 .. 1165060 . 13  rows= 94  width= 24 ) (actual time= 1045 . 014 .. 77327 . 862  rows= 197  loops= 1 )
                     Hash Cond: ("outer".mtp_addr_id = "inner".mtp_addr_id)
                     ->  Hash Join  (cost= 16161 . 78 .. 1165056 . 58  rows= 94  width= 22 ) (actual time= 1034 . 371 .. 77316 . 649  rows= 197  loops= 1 )
                           Hash Cond: ("outer".sccp_addr_id = "inner".sccp_addr_id)
                           ->  Seq Scan on sccp_msg_stat i  (cost= 0 . 00 .. 886548 . 24  rows= 52469124  width= 22 ) (actual time= 2 . 366 .. 50629 . 782  rows= 52469126  loops= 1 )
                           ->  Hash  (cost= 16161 . 78 .. 16161 . 78  rows= 1  width= 8 ) (actual time= 658 . 800 .. 658 . 800  rows= 26  loops= 1 )
                                 ->  Seq Scan on sccp_addr s  (cost= 0 . 00 .. 16161 . 78  rows= 1  width= 8 ) (actual time= 83 . 005 .. 658 . 684  rows= 26  loops= 1 )
                                       Filter: ((cgssn =  6 ) AND (cdssn =  7 ) AND ((cgpa)::text ~~ '7:99______'::text) AND ((cdpa)::text ~~ '1:79______'::text))
                     ->  Hash  (cost= 1 . 91 .. 1 . 91  rows= 91  width= 10 ) (actual time= 10 . 624 .. 10 . 624  rows= 92  loops= 1 )
                           ->  Seq Scan on mtp_addr m  (cost= 0 . 00 .. 1 . 91  rows= 91  width= 10 ) (actual time= 10 . 452 .. 10 . 541  rows= 92  loops= 1 )
               ->  Hash  (cost= 1 . 56 .. 1 . 56  rows= 4  width= 19 ) (actual time= 20 . 238 .. 20 . 238  rows= 4  loops= 1 )
                     ->  Seq Scan on link_addr l  (cost= 0 . 00 .. 1 . 56  rows= 4  width= 19 ) (actual time= 20 . 197 .. 20 . 227  rows= 4  loops= 1 )
                           Filter: (((link_id)::text = 'SL521595368'::text) OR ((link_id)::text = 'SL336465782'::text) OR ((link_id)::text = 'SL1804289383'::text) OR ((link_id)::text = 'SL35005211'::text))
         ->  Index Scan using time_intervals_css7_pk on time_intervals_css7 t  (cost= 0 . 00 .. 3 . 02  rows= 1  width= 4 ) (actual time= 1 . 062 .. 1 . 063  rows= 1  loops= 197 )
               Index Cond: (t.time_id = "outer".time_id)
               Filter: ((btime >= '2007-07-01 00:00:00+04'::timestamp with time zone) AND (btime <= '2007-08-31 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-31 00:00:00+04'::timestamp with time zone))

...

Рейтинг:

0 / 0

22.10.2007, 15:51

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34885441

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

создать индекс на таблицу sccp_msg_stat по полю sccp_addr_id

...

Рейтинг:

0 / 0

22.10.2007, 16:07

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34885535

pjatachok

Гость

LeXa NalBatсоздать индекс на таблицу sccp_msg_stat по полю sccp_addr_id
ну это понятно,
просто раньше в самом начале пытались уйти от таблицы sccp_msg_stat (52мл записи а теперь опять к ней возвращаемся)

...

Рейтинг:

0 / 0

22.10.2007, 16:28

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34885586

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

pjatachok LeXa NalBatсоздать индекс на таблицу sccp_msg_stat по полю sccp_addr_idну это понятно,
просто раньше в самом начале пытались уйти от таблицы sccp_msg_stat (52мл записи а теперь опять к ней возвращаемся)ТОТ запрос оптимизировали введением дополнительной таблицы. ЭТОТ - построением нужного индекса. имхо, эти два запроса, отличающиеся казалось бы только "опцей, иногда это есть в запросе, иногда нет", на самом деле являются принципально разными в смысле эффективных планов выполнения.

...

Рейтинг:

0 / 0

22.10.2007, 16:38

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34885724

pjatachok

Гость

LeXa NalBat, ВАУ!!!!! выполняется после введения индекса за 800мс!!!!!!! ОГРОМНОЕ СПАСИБО ЕЩЕ РАЗ

...

Рейтинг:

0 / 0

22.10.2007, 17:03

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34885785

pjatachok

Гость

а, это из кеша результаты взяты после повторения запроса....так выполняется где-то за 45сек..все же не 300сек, уже лучше

...

Рейтинг:

0 / 0

22.10.2007, 17:14

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34885810

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

pjatachokа, это из кеша результаты взяты после повторения запроса....так выполняется где-то за 45сек..все же не 300сек, уже лучшепо плану NestedLoop( SeqScan(sccp_addr), IndexScan(sccp_msg_stat) ) должен выполняться около секунды. давайте explain analyze.

...

Рейтинг:

0 / 0

22.10.2007, 17:22

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34885971

pjatachok

Гость

LeXa NalBat pjatachokа, это из кеша результаты взяты после повторения запроса....так выполняется где-то за 45сек..все же не 300сек, уже лучшепо плану NestedLoop( SeqScan(sccp_addr), IndexScan(sccp_msg_stat) ) должен выполняться около секунды. давайте explain analyze.

ситуация:
выполнил несколько других запросов, в том числе "тяжелых", а потом
EXPLAIN ANALYSE SELECT m.ni, m.opc, m.dpc, l.link_id, s.cgssn, s.cdssn, i.mtype, 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-8-31 0:0:0' )
AND ( t.etime BETWEEN '2007-7-1 0:0:0' AND '2007-8-31 0:0:0' )
AND link_id IN ( 'SL521595368', 'SL336465782', 'SL1804289383', 'SL35005211' )
AND s.cgssn=6 AND s.cdssn=7 AND s.cgpa LIKE '_:99______' AND s.cdpa LIKE '1:79______'
GROUP BY m.ni,m.opc,m.dpc, l.link_id, s.cgssn, s.cdssn, i.mtype;
результат:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

 HashAggregate  (cost= 19084 . 06 .. 19084 . 07  rows= 1  width= 31 ) (actual time= 43217 . 227 .. 43217 . 244  rows= 16  loops= 1 )
   ->  Nested Loop  (cost= 5 . 42 .. 19083 . 80  rows= 13  width= 31 ) (actual time= 149 . 868 .. 43187 . 854  rows= 11278  loops= 1 )
         ->  Nested Loop  (cost= 5 . 42 .. 19044 . 46  rows= 13  width= 29 ) (actual time= 145 . 103 .. 43107 . 428  rows= 11278  loops= 1 )
               ->  Nested Loop  (cost= 5 . 42 .. 19004 . 99  rows= 13  width= 33 ) (actual time= 92 . 067 .. 42921 . 316  rows= 11278  loops= 1 )
                     Join Filter: ("inner".link_addr_id = "outer".link_addr_id)
                     ->  Nested Loop  (cost= 5 . 42 .. 18853 . 65  rows= 94  width= 22 ) (actual time= 92 . 025 .. 42513 . 388  rows= 11334  loops= 1 )
                           ->  Seq Scan on sccp_addr s  (cost= 0 . 00 .. 16161 . 78  rows= 1  width= 8 ) (actual time= 25 . 238 .. 1030 . 528  rows= 70  loops= 1 )
                                 Filter: ((cgssn =  6 ) AND (cdssn =  7 ) AND ((cgpa)::text ~~ '_:99______'::text) AND ((cdpa)::text ~~ '1:79______'::text))
                           ->  Bitmap Heap Scan on sccp_msg_stat i  (cost= 5 . 42 .. 2683 . 23  rows= 691  width= 22 ) (actual time= 25 . 555 .. 592 . 376  rows= 162  loops= 70 )
                                 Recheck Cond: (i.sccp_addr_id = "outer".sccp_addr_id)
                                 ->  Bitmap Index Scan on sccp_msg_stat_sccp_addr_id  (cost= 0 . 00 .. 5 . 42  rows= 691  width= 0 ) (actual time= 22 . 696 .. 22 . 696  rows= 162  loops= 70 )
                                       Index Cond: (i.sccp_addr_id = "outer".sccp_addr_id)
                     ->  Seq Scan on link_addr l  (cost= 0 . 00 .. 1 . 56  rows= 4  width= 19 ) (actual time= 0 . 003 .. 0 . 031  rows= 4  loops= 11334 )
                           Filter: (((link_id)::text = 'SL521595368'::text) OR ((link_id)::text = 'SL336465782'::text) OR ((link_id)::text = 'SL1804289383'::text) OR ((link_id)::text = 'SL35005211'::text))
               ->  Index Scan using time_intervals_css7_pk on time_intervals_css7 t  (cost= 0 . 00 .. 3 . 02  rows= 1  width= 4 ) (actual time= 0 . 013 .. 0 . 014  rows= 1  loops= 11278 )
                     Index Cond: (t.time_id = "outer".time_id)
                     Filter: ((btime >= '2007-07-01 00:00:00+04'::timestamp with time zone) AND (btime <= '2007-08-31 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-31 00:00:00+04'::timestamp with time zone))
         ->  Index Scan using mtp_addr_pk on mtp_addr m  (cost= 0 . 00 .. 3 . 01  rows= 1  width= 10 ) (actual time= 0 . 004 .. 0 . 005  rows= 1  loops= 11278 )
               Index Cond: (m.mtp_addr_id = "outer".mtp_addr_id)
 Total runtime:  43217 . 390  ms
(записей:  20 )

тут же сразу повторяю тот же запрос EXPLAIN ANALYSE и результат:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

HashAggregate  (cost= 19084 . 06 .. 19084 . 07  rows= 1  width= 31 ) (actual time= 909 . 227 .. 909 . 244  rows= 16  loops= 1 )
   ->  Nested Loop  (cost= 5 . 42 .. 19083 . 80  rows= 13  width= 31 ) (actual time= 5 . 677 .. 889 . 441  rows= 11278  loops= 1 )
         ->  Nested Loop  (cost= 5 . 42 .. 19044 . 46  rows= 13  width= 29 ) (actual time= 5 . 669 .. 825 . 657  rows= 11278  loops= 1 )
               ->  Nested Loop  (cost= 5 . 42 .. 19004 . 99  rows= 13  width= 33 ) (actual time= 5 . 654 .. 742 . 487  rows= 11278  loops= 1 )
                     Join Filter: ("inner".link_addr_id = "outer".link_addr_id)
                     ->  Nested Loop  (cost= 5 . 42 .. 18853 . 65  rows= 94  width= 22 ) (actual time= 5 . 621 .. 395 . 019  rows= 11334  loops= 1 )
                           ->  Seq Scan on sccp_addr s  (cost= 0 . 00 .. 16161 . 78  rows= 1  width= 8 ) (actual time= 5 . 123 .. 293 . 484  rows= 70  loops= 1 )
                                 Filter: ((cgssn =  6 ) AND (cdssn =  7 ) AND ((cgpa)::text ~~ '_:99______'::text) AND ((cdpa)::text ~~ '1:79______'::text))
                           ->  Bitmap Heap Scan on sccp_msg_stat i  (cost= 5 . 42 .. 2683 . 23  rows= 691  width= 22 ) (actual time= 0 . 131 .. 1 . 282  rows= 162  loops= 70 )
                                 Recheck Cond: (i.sccp_addr_id = "outer".sccp_addr_id)
                                 ->  Bitmap Index Scan on sccp_msg_stat_sccp_addr_id  (cost= 0 . 00 .. 5 . 42  rows= 691  width= 0 ) (actual time= 0 . 075 .. 0 . 075  rows= 162  loops= 70 )
                                       Index Cond: (i.sccp_addr_id = "outer".sccp_addr_id)
                     ->  Seq Scan on link_addr l  (cost= 0 . 00 .. 1 . 56  rows= 4  width= 19 ) (actual time= 0 . 002 .. 0 . 027  rows= 4  loops= 11334 )
                           Filter: (((link_id)::text = 'SL521595368'::text) OR ((link_id)::text = 'SL336465782'::text) OR ((link_id)::text = 'SL1804289383'::text) OR ((link_id)::text = 'SL35005211'::text))
               ->  Index Scan using time_intervals_css7_pk on time_intervals_css7 t  (cost= 0 . 00 .. 3 . 02  rows= 1  width= 4 ) (actual time= 0 . 005 .. 0 . 005  rows= 1  loops= 11278 )
                     Index Cond: (t.time_id = "outer".time_id)
                     Filter: ((btime >= '2007-07-01 00:00:00+04'::timestamp with time zone) AND (btime <= '2007-08-31 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-31 00:00:00+04'::timestamp with time zone))
         ->  Index Scan using mtp_addr_pk on mtp_addr m  (cost= 0 . 00 .. 3 . 01  rows= 1  width= 10 ) (actual time= 0 . 003 .. 0 . 003  rows= 1  loops= 11278 )
               Index Cond: (m.mtp_addr_id = "outer".mtp_addr_id)
 Total runtime:  909 . 399  ms
(записей:  20 )

вот так "прыгает" время выполнения запроса

...

Рейтинг:

0 / 0

22.10.2007, 18:01

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34886019

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

да, кэширование налицо. но в этом запросе с s.cgpa LIKE '_:99______' прочесывается в пять раз больше строк (11278), чем в предыдущем с s.cgpa LIKE '7:99______' (197).

...

Рейтинг:

0 / 0

22.10.2007, 18:22

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34888683

pjatachok

Гость

LeXa NalBat, итак, я понимаю, больше ничего нельзя сделать с этим запросом?

...

Рейтинг:

0 / 0

23.10.2007, 17:02

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34888811

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

попробовать "избыточную OLAP-like структуру"

например:

имеем таблицу t1 (f1 text)
12
13
23

надо ускорить запрос select count(*) from t1 where f1 like '_3';

поддерживаем триггерами таблицу t2 (f1 text, cnt integer)
12 1
_2 1
1_ 2
__ 3
13 1
_3 2
23 1
2_ 1

требуемый запрос эквивалентен быстрому select cnt from t2 where f1 = '_3'

однако при изменении одной строки в t1 надо будет в триггере изменить 2^(length(f1)) строк в t2

...

Рейтинг:

0 / 0

23.10.2007, 17:31

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34902963

pjatachok

Гость

LeXa NalBat, большое спасибо за предложение))))много для себя узнал на будущее)) однако в данном случае такое решение врядли применимо, поскольку как вы заметили число строк в новой таблице будет степенной функцией от числа строк в оригинальной) а их весьма много

...

Рейтинг:

0 / 0

29.10.2007, 19:40

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34903331

Vladimir Sitnikov

Гость

LeXa NalBat
например:

имеем таблицу t1 (f1 text)
12
13
23

надо ускорить запрос select count(*) from t1 where f1 like '_3';

поддерживаем триггерами таблицу t2 (f1 text, cnt integer)...
Зачем нужны все эти подчерки в таблице t2?
Для каждой строки из t1, достаточно хранить O(length) строк в таблице t2. Никаких степеней двойки.
t1:
string_id text1124213323

t2 +index on (subtext, position):
string_id position subtext10124112412420132133023313
Соответсвенно, запрос t1.text like '_3' заменяется на t2.subtext='3' and t2.position=1 и т.п.

pjatachokкак вы заметили число строк в новой таблице будет степенной функцией
На заметку: степенная функция имеет вид x ⁿ , где n -- константа. Функция вида n ^x называется показательной.

...

Рейтинг:

0 / 0

30.10.2007, 01:43

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34903754

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

pjatachokчисло строк в новой таблице будет степенной функцией от числа строк в оригинальнойне будет ни степенной функцией, ни показательной (как поправил Vladimir Sitnikov). кол-во строк в таблице t2 будет в пределах от N*((k+1)/k)^(length(f1)) до N*2^(length(f1)), где k - основание системы счисления в f1. то есть линейная функция от N.

Vladimir Sitnikovt2 +index on (subtext, position):
string_id position subtextпроблема не в медленном поиске по t1, а в медленном аггрегировании полученных строк. продолжая вашу идею, может быть для этого использовать таблицу t2(count,position,subtext)?

Vladimir SitnikovСоответсвенно, запрос t1.text like '_3' заменяется на t2.subtext='3' and t2.position=1 и т.п.на что в вашем варианте заменяется запрос t1.text like '1___2___'? будет ли он быстрым?

...

Рейтинг:

0 / 0

30.10.2007, 10:32

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34904672

Vladimir Sitnikov

Гость

LeXa NalBatпроблема не в медленном поиске по t1, а в медленном аггрегировании полученных строк.O__O
Разве вообще возможна проблема в "медленном агрегировании"?
Насколько я смог понять, по условию Filter: ((cgssn = 6) AND (cdssn = 7) AND ((cgpa)::text ~~ '_:99______'::text) AND ((cdpa)::text ~~ '1:79______'::text)) из таблицы sccp_addr выбралось 70 строк из 500К.
Слабо верится, что остальные 40 секунд ушли на загрузку/агрегацию полученных 11К строк.
Вот я и подумал, а почему бы не выбирать эти самые 70 строк из индекса?

LeXa NalBatне будет ни степенной функцией, ни показательной... то есть линейная функция от N.Известно ли вам, что линейная функция является степенной?

LeXa NalBatна что в вашем варианте заменяется запрос t1.text like '1___2___'? будет ли он быстрым?Вообще говоря, нужно лишь выбрать наиболее селективную подпоследовательность без подчерков и искать по ней (из-за того, что в postgres нет index-only доступа к данным).
t1.text like '1___2___' можно либо оставить "как есть" (создав индекс по колонке text), либо заменить на t2.subtext like '1%' and position=0, либо на t2.subtext like '2%' and position=4. В случае, когда цифры располагаются поодиночке, возможно, такой индекс не даст выигрыша.

...

Рейтинг:

0 / 0

30.10.2007, 14:02

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34904902

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

Vladimir SitnikovРазве вообще возможна проблема в "медленном агрегировании"?конечно. в оригинале имеем джоин и аггрегирование более 10 тысяч строк. с помощью моего совета аггрегирование делается в триггерах в дополнительной таблице на этапе изменения данных. а выборка получается быстрой.

Vladimir SitnikovНасколько я смог понять, по условию Filter: ((cgssn = 6) AND (cdssn = 7) AND ((cgpa)::text ~~ '_:99______'::text) AND ((cdpa)::text ~~ '1:79______'::text)) из таблицы sccp_addr выбралось 70 строк из 500К.
Слабо верится, что остальные 40 секунд ушли на загрузку/агрегацию полученных 11К строк.seqscan работал секунду (actual time=1030.528), в то время как внутренняя часть nested-loop - 41 секунду (actual time=592.376 loops=70).

Vladimir SitnikovИзвестно ли вам, что линейная функция является степенной?точно! с показателем единица.

Vladimir Sitnikov LeXa NalBatна что в вашем варианте заменяется запрос t1.text like '1___2___'? будет ли он быстрым?Вообще говоря, нужно лишь выбрать наиболее селективную подпоследовательность без подчерков и искать по нейили можно сделать джоин выборок по индексу (position,substring) с условиями like '1%' и like '2%'. сомневаюсь, что это будет быстро.

и сработает это только для таблицы t2(string_id,..), а для таблицы t2(count,..) не получится найти искомый count(*).

Vladimir Sitnikovиз-за того, что в postgres нет index-only доступа к данныма это здесь каким образом влияет? объясните пожалуйста подробнее.

Vladimir Sitnikovt1.text like '1___2___' можно либо оставить "как есть" (создав индекс по колонке text), либо заменить на t2.subtext like '1%' and position=0, либо на t2.subtext like '2%' and position=4. В случае, когда цифры располагаются поодиночке, возможно, такой индекс не даст выигрыша.да, думаю выигрыша не даст. в отличие от предложенного мной способа. это ответ на ваш вопрос "зачем нужны все эти подчерки в таблице t2".

...

Рейтинг:

0 / 0

30.10.2007, 15:07

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34905346

Vladimir Sitnikov

Гость

LeXa NalBat Vladimir SitnikovРазве вообще возможна проблема в "медленном агрегировании"?конечно. в оригинале имеем джоин и аггрегирование более 10 тысяч строк. с помощью моего совета аггрегирование делается в триггерах в дополнительной таблице на этапе изменения данных. а выборка получается быстрой.И что? Разве тормозила агрегация как таковая?
По плану выполнения, агрегация выполняется на самом последнем шаге (если её можно перенести на более ранние этапы, это, конечно же стоит делать). Но, вы, ведь, предлагаете не переносить агрегацию на ранние этапы, а на триггерах предвычислять результаты полного запроса?

LeXa NalBatseqscan работал секунду (actual time=1030.528), в то время как внутренняя часть nested-loop - 41 секунду (actual time=592.376 loops=70).
А что входит во внутренюю часть nested loop? Очень слабо верится в то, что на поиск 11К строк в nested-loop'е ушло 40 секунд.

LeXa NalBat Vladimir Sitnikov LeXa NalBatна что в вашем варианте заменяется запрос t1.text like '1___2___'? будет ли он быстрым?Вообще говоря, нужно лишь выбрать наиболее селективную подпоследовательность без подчерков и искать по нейили можно сделать джоин выборок по индексу (position,substring) с условиями like '1%' и like '2%'. сомневаюсь, что это будет быстро.
...
а это здесь каким образом влияет? объясните пожалуйста подробнее.
Зачем сомневаться? Для выборки по индексу like '1%' придётся блоки индекса, удволетворяющие условию (ind1 блоков) и блоки таблицы (tbl1). Для выборки по индексу like '2%' придётся прочитать какие-то другие блоки индекса (ind2) и блоки таблицы (как новые, так и старые, tbl2 штук).
В результате, если мы сделаем 2 обращения к индексам, нам потребуется прочитать ind1+tbl1+ind2+tbl2 блоков.
Другое дело, тот же ответ мы можем получить, просмотрев лишь один раз 1-ый индекc -- ведь достоверно известно, что он не пропустит ни одной строки. В то же время, like '2%' непременно вернёт как нужные строки (которые мы уже видели при первом обращении), так и какие-то лишние. Какой смысл выбирать эти лишние строки и тратить на это дополнительное время?

Возможно, станет понятнее, если сразу записать условие поиска в виде t2.subtext like '1___2%'.

LeXa NalBatи сработает это только для таблицы t2(string_id,..), а для таблицы t2(count,..) не получится найти искомый count(*).
LeXa NalBatда, думаю выигрыша не даст. в отличие от предложенного мной способа. это ответ на ваш вопрос "зачем нужны все эти подчерки в таблице t2".
Честно говоря, в оригинальном запросе я count(*) не увидел. Не подскажете, как count упростит оригинальный запрос?

...

Рейтинг:

0 / 0

30.10.2007, 16:59

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#34905620

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

Vladimir SitnikovИ что? Разве тормозила агрегация как таковая?нет, тормозил джоин, необходимый для последующей аггрегации, внутренняя часть nested loop.

Vladimir SitnikovПо плану выполнения, агрегация выполняется на самом последнем шаге (если её можно перенести на более ранние этапы, это, конечно же стоит делать).боюсь, что не получится :-(

Vladimir SitnikovНо, вы, ведь, предлагаете не переносить агрегацию на ранние этапы, а на триггерах предвычислять результаты полного запроса?да, совершенно верно

Vladimir SitnikovА что входит во внутренюю часть nested loop?

Честно говоря, в оригинальном запросе я count(*) не увидел.в этом посте запрос и план. во внутренней части - bitmap heap, index scans. в оригинальном запросе - SUM(i.count).

Vladimir SitnikovОчень слабо верится в то, что на поиск 11К строк в nested-loop'е ушло 40 секунд.мне тоже это кажется медленным. хотя если эти строки равномерно разбросаны по страницам, то в худшем случае получится 11K страниц. :-(

Vladimir SitnikovЗачем сомневаться? Для выборки по индексу like '1%' придётся блоки индекса, удволетворяющие условию (ind1 блоков) и блоки таблицы (tbl1). Для выборки по индексу like '2%' придётся прочитать какие-то другие блоки индекса (ind2) и блоки таблицы (как новые, так и старые, tbl2 штук).
В результате, если мы сделаем 2 обращения к индексам, нам потребуется прочитать ind1+tbl1+ind2+tbl2 блоков.
Другое дело, тот же ответ мы можем получить, просмотрев лишь один раз 1-ый индекc -- ведь достоверно известно, что он не пропустит ни одной строки. В то же время, like '2%' непременно вернёт как нужные строки (которые мы уже видели при первом обращении), так и какие-то лишние. Какой смысл выбирать эти лишние строки и тратить на это дополнительное время?

Возможно, станет понятнее, если сразу записать условие поиска в виде t2.subtext like '1___2%'.считаем

пусть условию like '1%' удовлетворяет N1 строк (в t1 и в t2), like '2%' - N2

план IndexScan(t1,like '1%'),Filter(like '2%') требует просмотра N1 строк ширины w1 в таблице t1. цена ~ N1*w1

план IndexScan(t2,like '1%'),IndexScan(t2,like '2%') имеет цену ~ (N1+N2)*w2, где w2 - ширина строки в t2

и что же? какой план быстрее?

Vladimir SitnikovВообще говоря, нужно лишь выбрать наиболее селективную подпоследовательность без подчерков и искать по ней (из-за того, что в postgres нет index-only доступа к данным).все-таки непонятно. какое отношение отсутствие в постгресе index only scan имеет к данной ситуации? а именно, если бы в постгресе был index only scan, то надо было бы выбрать другой план вместо "выбрать наиболее селективную подпоследовательность без подчерков и искать по ней"?

Vladimir SitnikovНе подскажете, как count упростит оригинальный запрос?вы просите разжевать, какие именно колонки надо сгруппировать при создании t2, и как переписывается оригинальный запрос?

...

Рейтинг:

0 / 0

30.10.2007, 18:11

| Ответить | Цитировать | Написать

помогите оптимизировать запрос, пожалуйста

#35151070

pjatachok

Гость

снова здравствуйте)))

скажите, пожалуйста, каким образом к таблице

CREATE TABLE sccp_traf_stat_grouped AS
SELECT time_id, link_addr_id, mtp_addr_id, sum(count) as cnt
FROM sccp_traf_stat
GROUP BY time_id, link_addr_id, mtp_addr_id;

можно добавить констраинт

CONSTRAINT sccp_traf_stat_grouped_time_id_fk FOREIGN KEY (time_id)
REFERENCES time_intervals_css7 (time_id) ON UPDATE NO ACTION ON DELETE CASCADE

?????

в таблице sccp_traf_stat на базе которой строится новая есть этот же констраинт(по time_id)

...

Рейтинг:

0 / 0

22.02.2008, 17:35

| Ответить | Цитировать | Написать

57 сообщений из 57, показаны все 3 страниц

все

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / помогите оптимизировать запрос, пожалуйста

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?all=1&fid=53&tid=2004577]:	0ms
get settings:	7ms
get forum list:	13ms
check forum access:	3ms
check topic access:	3ms
track hit:	36ms
get topic data:	9ms
get forum data:	2ms
get page messages:	64ms
get tp. blocked users:	1ms
others:	208ms

total:	346ms