This may sound more elaborate than it's worth, but I don't know of
a better way to avoid a table scan.
You want to index on a computed value that is a common prefix of your
FROM and TO fields.
The next step is to search on a fixed SET of prefixes of different
lengths. For example, some of your ran
On Tue, Oct 12, 2004 at 04:29:36PM +0200, Gabriele Bartolini wrote:
> FYI I set it to 1000 (the maximum) and I reduced the query's estimated time
> by the 90% (from 4ms to 4000ms) although much slower than the index
> scan (200ms).
Note that the estimated times are _not_ in ms. They are in mul
Hi Kris,
>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
I got your point now. I had not understood it last night but it makes really
sense.
>which is roughtly 10% of the table. I imagine the query
ssage -
From: "Kris Jurka" <[EMAIL PROTECTED]>
To: "Gabriele Bartolini" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, October 11, 2004 5:17 PM
Subject: Re: [PERFORM] Normal case or bad query plan?
>
>
Gabriele Bartolini <[EMAIL PROTECTED]> writes:
> 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
>>
>>
Hi Tom,
thanks for your interest.
At 23.33 11/10/2004, Tom Lane wrote:
Gabriele Bartolini <[EMAIL PROTECTED]> writes:
> QUERY PLAN
>
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)
>
Gabriele Bartolini <[EMAIL PROTECTED]> writes:
> QUERY PLAN
> -
> Seq Scan on ip2location (cost=0.00..30490.65 rows=124781 wid
Hi guys,
please consider this scenario. I have this table:
CREATE TABLE ip2location (
ip_address_from BIGINT NOT NULL,
ip_address_to BIGINT NOT NULL,
id_location BIGINT NOT NULL,
PRIMARY KEY (ip_address_from, ip_address_to)
);
I created a cluster on its primary key, by running:
C
