Re: Normal case or bad query plan?

Поиск
Список
Период
Сортировка
От Kris Jurka
Тема Re: Normal case or bad query plan?
Дата
Msg-id Pine.BSO.4.56.0410111610350.32091@leary.csoft.net
обсуждение исходный текст
Ответ на Normal case or bad query plan?  (Gabriele Bartolini <angusgb@tin.it>)
Ответы Re: Normal case or bad query plan?  ("Gabriele Bartolini" <angusgb@tin.it>)
Список pgsql-performance

On Mon, 11 Oct 2004, Gabriele Bartolini wrote:

> ---------------------------------------------------------------------------------------------------------------------
>   Seq Scan on ip2location  (cost=0.00..30490.65 rows=124781 width=8)
> (actual time=5338.120..40237.283 rows=1 loops=1)
>     Filter: ((1040878301::bigint >= ip_address_from) AND
> (1040878301::bigint <= ip_address_to))
>   Total runtime: 40237.424 ms
>

I believe the problem is that pg's lack of cross-column statistics is
producing the poor number of rows estimate.  The number of rows mataching
just the first 1040878301::bigint >= ip_address_from condition is 122774
which is roughtly 10% of the table.  I imagine the query planner
believes that the other condition alone will match the other 90% of the
table.  The problem is that it doesn't know that these two ranges'
intersection is actually tiny.  The planner assumes a complete or nearly
complete overlap so it thinks it will need to fetch 10% of the rows from
both the index and the heap and chooses a seqscan.

Kris Jurka

В списке pgsql-performance по дате отправления:

Предыдущее
От: Christopher Browne
Дата:
Сообщение: Re: IBM P-series machines (was: Excessive context
Следующее
От: Francisco Reyes
Дата:
Сообщение: Re: why my query is not using index??