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
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? |