Re: Thinking About Correlated Columns (again)

От: Thomas Kellerer
Тема: Re: Thinking About Correlated Columns (again)
Дата: ,
Msg-id: kn0r9o$5je$1@ger.gmane.org
(см: обсуждение, исходный текст)
Ответ на: Thinking About Correlated Columns (again)  (Shaun Thomas)
Ответы: Re: Thinking About Correlated Columns (again)  (Shaun Thomas)
Список: pgsql-performance

Скрыть дерево обсуждения

Thinking About Correlated Columns (again)  (Shaun Thomas, )
 Re: Thinking About Correlated Columns (again)  (Heikki Linnakangas, )
  Re: Thinking About Correlated Columns (again)  (Shaun Thomas, )
  Re: Thinking About Correlated Columns (again)  (Nikolas Everett, )
   Re: Thinking About Correlated Columns (again)  (eggyknap, )
  Re: Thinking About Correlated Columns (again)  (Gavin Flower, )
 Re: Thinking About Correlated Columns (again)  (Craig James, )
  Re: Thinking About Correlated Columns (again)  (Andrew Dunstan, )
  Re: Thinking About Correlated Columns (again)  (Gavin Flower, )
   Re: Thinking About Correlated Columns (again)  (Craig James, )
 Re: Thinking About Correlated Columns (again)  (Thomas Kellerer, )
  Re: Thinking About Correlated Columns (again)  (Shaun Thomas, )

Shaun Thomas wrote on 15.05.2013 17:31:
> Hi!
>
> This has been a pain point for quite a while. While we've had several
> discussions in the area, it always seems to just kinda trail off and
> eventually vanish every time it comes up.
>
> A really basic example of how bad the planner is here:
>
> CREATE TABLE foo AS
> SELECT a.id, a.id % 1000 AS col_a, a.id % 1000 AS col_b
>    FROM generate_series(1, 1000000) a(id);
>
> CREATE INDEX idx_foo_ab ON foo (col_a, col_b);
>
> Index Scan using idx_foo_ab on foo  (cost=0.00..6.35 rows=1 width=12)
>             (actual time=0.030..3.643 rows=1000 loops=1)
>    Index Cond: ((col_a = 50) AND (col_b = 50))
>
> Hey, look! The row estimate is off by a factor of 1000. This
> particular case doesn't suffer terribly from the mis-estimation, but
> others do. Boy, do they ever.

What happens if you create one index for each column? (instead of one combined index)

For your example it does not seem to improve the situation, but maybe things get better with the "bad" queries?





В списке pgsql-performance по дате сообщения:

От: Craig James
Дата:
Сообщение: Re: Thinking About Correlated Columns (again)
От: Andrea Suisani
Дата:
Сообщение: Re: [OT] linux 3.10 kernel will improve ipc,sysv semaphore scalability