Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Я в шоке, у слона поехала крыша?  [new]
Мужик
Member

Откуда:
Сообщений: 160
8.1.9 или 8.3.0

2 таблицы
create table a(
  id bigint,
  a_date date
);

create index a_a_date on a(a_date);

create table b(
  id bigint,
  a_id bigint references a(id),
  value varchar
);

B -- 4 500 000 записей, A -- 650 000.

Запрос
analyze a;
analyze b;

select *
from a
  join b on b.a_id = a.id
where a.a_date between '2008-01-01' and '2008-01-02';

План
 Merge Join  (cost=256.32..63154.32 rows=5429 width=8) (actual time=14243.898..14274.528 rows=1334 loops=1)                                                 
   Merge Cond: (b.a_id = a.id)                                                                                                                          
   ->  Index Scan using fki_a_a_id_fkey on b  (cost=0.00..212218.79 rows=4646235 width=16) (actual time=21.944..12167.407 rows=1008296 loops=1) 
   ->  Sort  (cost=256.31..262.93 rows=2650 width=8) (actual time=20.939..22.847 rows=1334 loops=1)                                                         
         Sort Key: a.id                                                                                                                                     
         Sort Method:  quicksort  Memory: 40kB                                                                                                              
         ->  Index Scan using a_a_date on a  (cost=0.00..105.63 rows=2650 width=8) (actual time=17.150..19.869 rows=608 loops=1)   
               Index Cond: ((a_date >= '2008-01-01'::date) AND (a_date <= '2008-01-02'::date))                                                        
 Total runtime: 14276.534 ms                                                                                                                                

Запрос
select *
from a
where a_date between '2008-01-01' and '2008-01-02' 

Выполняется <10 мс.

Запрос (для реалистичности 500 000 - 502 000 это примерно те id из a, которые попадают в указанный интервал дат)
select *
from (select generate_series as id from generate_series(500000, 502000)) a
  join b on b.a_id = a.id

План
 Nested Loop  (cost=0.00..119415.38 rows=1155043 width=8) (actual time=1.072..44.878 rows=3837 loops=1)                                       
   ->  Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (actual time=1.000..3.916 rows=2001 loops=1)                    
   ->  Index Scan using fki_a_a_id_fkey on b  (cost=0.00..104.97 rows=1155 width=16) (actual time=0.008..0.012 rows=2 loops=2001) 
         Index Cond: (b.a_id = generate_series.generate_series)                                                                           
 Total runtime: 50.226 ms                                                                                                                     

Если id из a сначала выбрать в темповую таблицу, а потом поджойнить с a, то все летает.

Что вообще происходит, а? :) Его FK что ли смущает?
15 фев 08, 14:19    [5295203]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2887
попробуйте set enable_mergejoin to off
15 фев 08, 14:58    [5295647]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Мужик
Member

Откуда:
Сообщений: 160
LeXa NalBat
попробуйте set enable_mergejoin to off

Скорость та же, теперь вместо merge везде hash.
15 фев 08, 15:01    [5295682]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Мужик
Member

Откуда:
Сообщений: 160
Отключил и hash... Помогло, но не вариант, ведь такие запросы пишу не я, они генерируются приложением.
15 фев 08, 15:03    [5295692]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2887
Мужик
Отключил и hash... Помогло, ...
покажите explain analyze
15 фев 08, 15:15    [5295812]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Мужик
Member

Откуда:
Сообщений: 160
LeXa NalBat
покажите explain analyze

 Nested Loop  (cost=0.00..307235.29 rows=5429 width=8) (actual time=0.106..13.626 rows=1334 loops=1)                                              
   ->  Index Scan using a_a_date on a  (cost=0.00..105.63 rows=2650 width=8) (actual time=0.067..1.270 rows=608 loops=1) 
         Index Cond: ((a_date >= '2008-01-01'::date) AND (a_date <= '2008-01-02'::date))                                                    
   ->  Index Scan using fki_a_a_id_fkey on b  (cost=0.00..101.46 rows=1155 width=16) (actual time=0.007..0.011 rows=2 loops=608)      
         Index Cond: (b.a_id = a.id)                                                                                                          
 Total runtime: 15.589 ms
15 фев 08, 15:21    [5295860]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2887
попробуйте собрать более подробную статистику по таблице(ам) с помощью "ALTER TABLE ... ALTER COLUMN ... SET STATISTICS" или default_statistics_target, чтобы устранить ошибку в оценке кол-ва строк
Nested Loop  (rows=5429) (actual rows=1334)                                              
-> Index Scan using a_a_date on a (rows=2650) (actual rows=608)

и изменить параметры *_cost, effective_cache_size, чтобы на разных планах на большинстве ваших запросов уменьшить ошибку в оценке cost, которая должна быть пропорциональна actual time
Merge Join  (cost=63154.32) (actual time=14274.528) -- 4.4
-> Index Scan using fki_a_a_id_fkey on b (cost=212218.79) (actual time=12167.407) -- 17

Nested Loop (cost=307235.29) (actual time=13.626) -- 22
-> Index Scan using a_a_date on a (cost=105.63) (actual time=1.270) -- 83
15 фев 08, 15:46    [5296070]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Мужик
Member

Откуда:
Сообщений: 160
LeXa NalBat
попробуйте собрать более подробную статистику по таблице(ам) с помощью "ALTER TABLE ... ALTER и

Вах! Собрал на 100, теперь все летает! А от увеличения этого параметра для всей базы ничего не пострадает?
15 фев 08, 16:00    [5296201]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2887
Мужик
А от увеличения этого параметра для всей базы ничего не пострадает?
планы могут измениться. скорее всего в лучшую сторону. но для некоторых запросов может оказаться и наоборот :-)
15 фев 08, 16:15    [5296339]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Мужик
Member

Откуда:
Сообщений: 160
Мужик
LeXa NalBat
попробуйте собрать более подробную статистику по таблице(ам) с помощью "ALTER TABLE ... ALTER и

Вах! Собрал на 100, теперь все летает! А от увеличения этого параметра для всей базы ничего не пострадает?


Не помогло... как только расширяю диапазон дат, о5 выбирает не тот план... С настройками *cost игрался. Что только не делал, получается только изменить index scan на seq scan, в остальном все то же самое. Effective_cache_size -- 12Гб, база около 40Гб. Пробовал на других сервере с effective_cache_size 1 Гб и 128Мб -- все то же самое. Статистику собрал на уровне 1000, тоже не помогло. И еще странный момент в плане с merg'ом:

Merge Join (cost=256.32..63154.32 rows=5429 width=8) (actual time=14243.898..14274.528 rows=1334 loops=1)
Merge Cond: (b.a_id = a.id)
-> Index Scan using fki_a_a_id_fkey on b (cost=0.00..212218.79 rows=4646235 width=16) (actual time=21.944..12167.407 rows=1008296 loops=1)
-> Sort (cost=256.31..262.93 rows=2650 width=8) (actual time=20.939..22.847 rows=1334 loops=1)
Sort Key: a.id
Sort Method: quicksort Memory: 40kB
-> Index Scan using a_a_date on a (cost=0.00..105.63 rows=2650 width=8) (actual time=17.150..19.869 rows=608 loops=1)
Index Cond: ((a_date >= '2008-01-01'::date) AND (a_date <= '2008-01-02'::date))
Total runtime: 14276.534 ms

Как такое может быть, если судя по документации
It's important to note that the cost of an upper-level node includes the cost of all its child nodes. 

И почему nested loop так дорого стоит estimate и так дешево real?
17 фев 08, 12:53    [5300289]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Nick Gazaloff
Member

Откуда:
Сообщений: 124
а сделайте-ка

REINDEX TABLE b;
17 фев 08, 13:08    [5300319]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Мужик
Member

Откуда:
Сообщений: 160
Nick Gazaloff
а сделайте-ка

REINDEX TABLE b;

reindex и analyze было первое, что я сделал.
17 фев 08, 13:21    [5300342]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Гость_0
Guest
у Вас действительно есть 12 гигабайт памяти (Effective_cache_size -- 12Гб) под дисковой кеш ? это не ошибка ?
17 фев 08, 13:58    [5300394]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Мужик
Member

Откуда:
Сообщений: 160
Гость_0
у Вас действительно есть 12 гигабайт памяти (Effective_cache_size -- 12Гб) под дисковой кеш ? это не ошибка ?

Да, их там всего 16.
17 фев 08, 15:34    [5300509]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2887
"B -- 4 500 000 записей, A -- 650 000" - получается в среднем в таблице В приходится 7 строк на одну строку таблицы А.

в интервале от 2008-01-01 до 2008-01-02 получается 1334 строк и 608 строк, то есть отношение равно 2.2.

постгрес оценивает "Index Scan using fki_a_a_id_fkey on b (rows=1155)", то есть ошибается более чем в 100 раз.

надо попытаться побороть эту ошибку собрав более подробную статистику по таблице В.
18 фев 08, 11:37    [5302846]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Мужик
Member

Откуда:
Сообщений: 160
LeXa NalBat
"B -- 4 500 000 записей, A -- 650 000" - получается в среднем в таблице В приходится 7 строк на одну строку таблицы А.

в интервале от 2008-01-01 до 2008-01-02 получается 1334 строк и 608 строк, то есть отношение равно 2.2.

постгрес оценивает "Index Scan using fki_a_a_id_fkey on b (rows=1155)", то есть ошибается более чем в 100 раз.

надо попытаться побороть эту ошибку собрав более подробную статистику по таблице В.


На самом деле он не в 100, а в 500 раз ошибается: на каждую запись из B либо 0 либо 2 записи из А, но как ему это объяснить? Я уже собрал максимальную статистику для всех столбцов и А и В. Оценочное количество строк упало до 13и, рабочий интервал дат можно немного расширить, но не до необходимых пределов. План с merg'ом начинает выигрывать, когда количество строк из В раз в 10 выше, чем то, с которым начинает выбирать этот план постгрес...
18 фев 08, 12:35    [5303287]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2887
Мужик
на каждую запись из B либо 0 либо 2 записи из А
не понятно

или "на каждую запись из A либо 0 либо 2 записи из B"?

и почему тогда в B в семь раз больше строк, чем в A?

Мужик
Оценочное количество строк упало до 13и
покажите explain analyze по планам nested loop и merge join
18 фев 08, 12:59    [5303488]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Мужик
Member

Откуда:
Сообщений: 160
LeXa NalBat
Мужик
на каждую запись из B либо 0 либо 2 записи из А
не понятно

или "на каждую запись из A либо 0 либо 2 записи из B"?

и почему тогда в B в семь раз больше строк, чем в A?

Да, следует читать "на каждую запись из A приходится 0 либо 2 записи из B". Причем почти всегда их ровно 2.

Мужик
Оценочное количество строк упало до 13и
покажите explain analyze по планам nested loop и merge join[/quot]
Вот граничный случай:
Merge Join  (cost=762.68..57057.46 rows=14858 width=8) (actual time=5791.076..5964.570 rows=15798 loops=1)                                               
   Merge Cond: (b.a_id = a.id)                                                                                                                        
   ->  Index Scan using fki_a_a_id_fkey on b  (cost=0.00..191463.16 rows=4646237 width=16) (actual time=0.225..3851.333 rows=1022757 loops=1) 
   ->  Sort  (cost=762.67..781.10 rows=7372 width=8) (actual time=38.230..59.755 rows=15793 loops=1)                                                      
         Sort Key: a.id                                                                                                                                   
         Sort Method:  quicksort  Memory: 416kB                                                                                                           
         ->  Index Scan using a_a_date on a  (cost=0.00..289.10 rows=7372 width=8) (actual time=0.277..25.187 rows=7357 loops=1) 
               Index Cond: ((a_date >= '2008-01-01'::date) AND (a_date <= '2008-01-24'::date))                                                      
 Total runtime: 5985.886 ms                                                                                                                               

 Nested Loop  (cost=0.00..61336.70 rows=14858 width=8) (actual time=0.082..162.327 rows=15798 loops=1)                                              
   ->  Index Scan using a_a_date on a  (cost=0.00..289.10 rows=7372 width=8) (actual time=0.043..14.812 rows=7357 loops=1) 
         Index Cond: ((a_date >= '2008-01-01'::date) AND (a_date <= '2008-01-24'::date))                                                      
   ->  Index Scan using fki_a_a_id_fkey on b  (cost=0.00..8.12 rows=13 width=16) (actual time=0.007..0.011 rows=2 loops=7357)           
         Index Cond: (b.a_id = a.id)                                                                                                            
 Total runtime: 183.451 ms  
18 фев 08, 13:48    [5303908]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2887
Мужик
B -- 4 500 000 записей, A -- 650 000.

Да, следует читать "на каждую запись из A приходится 0 либо 2 записи из B". Причем почти всегда их ровно 2.
не сходится. 4 500 000 / 650 000 = 7 <> 2. почему так?

Мужик
Я уже собрал максимальную статистику для всех столбцов и А и В. Оценочное количество строк упало до 13и
можно попытаться понять, почему оценка 13 вместо 2, по содержимому pg_stats
19 фев 08, 11:16    [5308580]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Мужик
Member

Откуда:
Сообщений: 160
LeXa NalBat
Мужик
B -- 4 500 000 записей, A -- 650 000.

Да, следует читать "на каждую запись из A приходится 0 либо 2 записи из B". Причем почти всегда их ровно 2.
не сходится. 4 500 000 / 650 000 = 7 <> 2. почему так?

Почему не сходится? Около 1.3 млн записей из В имеют соответствующую из А, другие -- нет.
19 фев 08, 13:06    [5309597]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Мужик
Member

Откуда:
Сообщений: 160
LeXa NalBat
Мужик
Я уже собрал максимальную статистику для всех столбцов и А и В. Оценочное количество строк упало до 13и
можно попытаться понять, почему оценка 13 вместо 2, по содержимому pg_stats

Похоже дело в этом... у него там для таблицы В для столбца a_id n_distinct стоит в 7 раз меньше реального.
19 фев 08, 13:54    [5309966]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
Мужик
Member

Откуда:
Сообщений: 160
Собрал статистику по реальным/из pg_stats значению n_distinct для всех попавших в статистику столбцов базы. Вышло, что эти значения отличаются более чем в 5 раз у 8% столбцов, причем самые удручающие результаты у больших таблиц со множеством ссылок на относительно небольшие.

Отсюда вопрос. Можно ли как-нибудь задать формулу/значение для n_distinct для конкретной таблицы/столбца?
19 фев 08, 16:01    [5310911]     Ответить | Цитировать Сообщить модератору
 Re: Я в шоке, у слона поехала крыша?  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2887
Мужик
Похоже дело в этом... у него там для таблицы В для столбца a_id n_distinct стоит в 7 раз меньше реального.
можно попытаться разобраться самостоятельно, почему постгрес неправильно вычислил n_distinct, или написать разработчикам в рассылку.

Мужик
Можно ли как-нибудь задать формулу/значение для n_distinct для конкретной таблицы/столбца?
наверное можно сделать update в pg_statistic. но при первом же analyze постгрес опять пересчитает по-своему.
20 фев 08, 10:18    [5313901]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить