Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / LEFT JOIN 3-х таблиц / 14 сообщений из 14, страница 1 из 1
06.07.2015, 19:54
    #39000879
alexzf
alexzf
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
Добрый вечер!
Вопрос такой, есть три таблицы A (~319млн), B (~120млн)
Пишу запрос обычного JOIn (индексы есть)
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select 
	A.id,
	A.value,
        A.is_deleted
from 
	A  left join B on(A.id = B.id)
where 
	A.value = 'ZBTB44'
order by A.is_deleted



Ну так вот... естественно он будет очень долгим, в моем случае - секунд 8-9... и это очень долго.
Какие могут быть решения по ускорению?
Спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.07.2015, 20:20
    #39000897
vyegorov
vyegorov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
alexzf,

`EXPLAIN (analyze, buffers)` предоставьте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.07.2015, 21:41
    #39000923
Alexius
Alexius
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
предлагаю помедитировать над вопросом:

как выглядит план выполнения left join 3-х таблиц: A (~319млн) и B (~120млн)?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2015, 05:25
    #39001008
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
Alexiusпредлагаю помедитировать над вопросом:

как выглядит план выполнения left join 3-х таблиц: A (~319млн) и B (~120млн)?

Да почти классический даосский "хлопок одной ладонью".

Автору темы рекомендуется внимательно читать что он пишет и думать при этом.
И не забывать если задают вопросы о производительности всегда прикладывать результат explain analyze проблемного запроса.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2015, 08:15
    #39001019
Alexius
Alexius
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
Maxim BogukДа почти классический даосский "хлопок одной ладонью".

да, именно к этой истории отсылка и была.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2015, 10:34
    #39001116
alexzf
alexzf
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
Уважаемые, прошу прощения... вот эксплейн
Код: plsql
1.
2.
3.
4.
5.
6.
7.
"Nested Loop  (cost=314.23..197003.00 rows=10080 width=33)"
"  ->  Bitmap Heap Scan on snp_gene sg  (cost=314.23..29354.23 rows=10080 width=10)"
"        Recheck Cond: ((gene)::text = 'CTNND2'::text)"
"        ->  Bitmap Index Scan on snp_gene_gene_idx  (cost=0.00..311.71 rows=10080 width=0)"
"              Index Cond: ((gene)::text = 'CTNND2'::text)"
"  ->  Index Scan using snp_names_pkey on snp_names sn  (cost=0.00..16.62 rows=1 width=23)"
"        Index Cond: (atlas_id = sg.atlas_id)"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2015, 10:34
    #39001119
alexzf
alexzf
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
alexzf,

Ну и сам запрос
Код: plsql
1.
2.
3.
4.
5.
select 
	* 
from snp_names as sn left join snp_gene as sg on(sn.atlas_id = sg.atlas_id)
where
	sg.gene = 'CTNND2'
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2015, 13:07
    #39001364
vyegorov
vyegorov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
alexzf,

Просьба была предоставить `EXPLAIN (analyze, buffers)`, а не просто `EXPLAIN`.

Проанализируйте таблицы (`VACUUM ANALYZE`) и приведите новый выхлоп `EXPLAIN (analyze, buffers)`.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2015, 14:03
    #39001448
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
alexzfУважаемые, прошу прощения... вот эксплейн
Код: plsql
1.
2.
3.
4.
5.
6.
7.
"Nested Loop  (cost=314.23..197003.00 rows=10080 width=33)"
"  ->  Bitmap Heap Scan on snp_gene sg  (cost=314.23..29354.23 rows=10080 width=10)"
"        Recheck Cond: ((gene)::text = 'CTNND2'::text)"
"        ->  Bitmap Index Scan on snp_gene_gene_idx  (cost=0.00..311.71 rows=10080 width=0)"
"              Index Cond: ((gene)::text = 'CTNND2'::text)"
"  ->  Index Scan using snp_names_pkey on snp_names sn  (cost=0.00..16.62 rows=1 width=23)"
"        Index Cond: (atlas_id = sg.atlas_id)"



И опять вы не внимательно читаете. Просили explain analyze это раз.
Два в начальном посте у вас order by был которого в этом плане нет.
Три - получить 10000 строк от базы очень часто в 100 раз медленнее чем получить 100 строк и это надо учитывать.

PS: вероятнее всего у вас все время на диск уходит... так что больше памяти/больше shared buffers и/или ssd диски.
Ну и на размер базы и таблиц смотреть надо конечно.


--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2015, 14:18
    #39001462
alexzf
alexzf
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
Ок, сделал все что сказали... По поводу ордера это я просто запрос на память написал.

в запросе:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
select 
	* 
from snp_names as sn left join snp_gene as sg on(sn.atlas_id = sg.atlas_id)
where
	sg.gene = 'CTNND2'

	vacuum analyze snp_names



для таблицы snp_names vacuum analyze -> авторQuery returned successfully with no result in 175327 ms.
В данной таблице 119.258.056 строк

для таблицы snp_gene vacuum analyze -> авторQuery returned successfully with no result in 689503 ms.
В данной таблице 309.583.119 строк

А вот анализ запроса:


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
"Nested Loop  (cost=314.23..197003.00 rows=10080 width=33) (actual time=2.753..149.426 rows=8122 loops=1)"
"  Buffers: shared hit=33792 read=12425 written=1"
"  ->  Bitmap Heap Scan on snp_gene sg  (cost=314.23..29354.23 rows=10080 width=10) (actual time=2.690..50.691 rows=8122 loops=1)"
"        Recheck Cond: ((gene)::text = 'CTNND2'::text)"
"        Buffers: shared read=5498"
"        ->  Bitmap Index Scan on snp_gene_gene_idx  (cost=0.00..311.71 rows=10080 width=0) (actual time=1.613..1.613 rows=8122 loops=1)"
"              Index Cond: ((gene)::text = 'CTNND2'::text)"
"              Buffers: shared read=22"
"  ->  Index Scan using snp_names_pkey on snp_names sn  (cost=0.00..16.62 rows=1 width=23) (actual time=0.010..0.011 rows=1 loops=8122)"
"        Index Cond: (atlas_id = sg.atlas_id)"
"        Buffers: shared hit=33792 read=6927 written=1"
"Total runtime: 151.475 ms"




Делетов и апдейтов не было по этим таблицам. В принципе они всегда будут только на select.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2015, 14:24
    #39001468
alexzf
alexzf
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
И еще совет... ну это конечно на всякий..
денормализация? значительно облегчит выборку?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2015, 14:35
    #39001475
alexzf
alexzf
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
Да, диски SSD, но вот shared_buffers = 24MB из 32G.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2015, 14:42
    #39001481
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
alexzfДа, диски SSD, но вот shared_buffers = 24MB из 32G.

Поставьте 8Gb shared buffers это раз.
Два а вас что не устраивает 150ms на почти 10000 строк???? Это 0.015ms на строку, вполне приличное время помоему.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2015, 17:54
    #39001655
alexzf
alexzf
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN 3-х таблиц
Maxim Boguk,

Всем спасиб!

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
"Nested Loop  (cost=0.00..8134.25 rows=26 width=32) (actual time=0.544..65.136 rows=113 loops=1)"
"  Buffers: shared hit=1254 read=1577"
"  ->  Seq Scan on snp_gene sg  (cost=0.00..7917.53 rows=26 width=10) (actual time=0.528..64.365 rows=113 loops=1)"
"        Filter: ((gene)::text = 'CTNND2'::text)"
"        Buffers: shared hit=802 read=1577"
"  ->  Index Scan using snp_names_pkey on snp_names sn  (cost=0.00..8.32 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=113)"
"        Index Cond: (atlas_id = sg.atlas_id)"
"        Buffers: shared hit=452"
"Total runtime: 65.214 ms"



Вот такие вот результаты.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / LEFT JOIN 3-х таблиц / 14 сообщений из 14, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 522ms
Закрыть
start [/forum/topic.php?fid=53&mobile=1&tid=1997900]:
 0ms
get settings:
 8ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 209ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 47ms
get tp. blocked users:
 1ms
others: 224ms
total:  522ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]