Re: Normal case or bad query plan?

From: Kris Jurka <books(at)ejurka(dot)com>
To: Gabriele Bartolini <angusgb(at)tin(dot)it>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Normal case or bad query plan?
Date: 2004-10-11 21:17:24
Message-ID: Pine.BSO.4.56.0410111610350.32091@leary.csoft.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Janning Vygen 2004-10-11 21:26:02 Re: why my query is not using index??
Previous Message Gabriele Bartolini 2004-10-11 21:05:59 Normal case or bad query plan?