Re: Normal case or bad query plan?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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:33:57
Message-ID: 23515.1097530437@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Gabriele Bartolini <angusgb(at)tin(dot)it> writes:
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------
> 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

> Is this a normal case or should I worry? What am I missing?

The striking thing about that is the huge difference between estimated
rowcount (124781) and actual (1). The planner would certainly have
picked an indexscan if it thought the query would select only one row.

I suspect that you haven't ANALYZEd this table in a long time, if ever.
You really need reasonably up-to-date ANALYZE stats if you want the
planner to do an adequate job of planning range queries. It may well be
that you need to increase the analyze statistics target for this table,
also --- in BIGINT terms the distribution is probably pretty irregular,
which will mean you need finer-grain statistics to get good estimates.

(BTW, have you looked at the inet datatype to see if that would fit your
needs?)

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Rosser Schwarz 2004-10-11 22:03:07 Re: Understanding explains
Previous Message Janning Vygen 2004-10-11 21:26:02 Re: why my query is not using index??