Jayadevan <maymala.jayadevan@gmail.com> wrote:
> "Merge Join (cost=9268.34..26193.41 rows=6282 width=24) (actual time=892.188..892.190 rows=1 loops=1)"
> " Merge Cond: (l.id = b.location_id)"
> " -> Index Scan using locations_pkey on locations l (cost=0.42..15739.22 rows=438386 width=24) (actual
time=0.022..865.025rows=336605 loops=1)"
> " -> Sort (cost=9267.84..9283.54 rows=6282 width=8) (actual time=1.329..1.330 rows=1 loops=1)"
> " Sort Key: b.location_id"
> " Sort Method: quicksort Memory: 25kB"
> " -> Index Scan using ix_end_start_ip on blocks b (cost=0.43..8871.54 rows=6282 width=8) (actual
time=0.573..1.268rows=1 loops=1)"
> " Index Cond: ((3721196957::bigint <= end_ip) AND (3721196957::bigint >= start_ip))"
> "Total runtime: 892.439 ms"
This is the query which needs to be optimized. When I multiply the
runtime of this function's query by the estimated number of
function calls, I get 2.6 hours.
Copying the query from the first email on the thread:
SELECT l.id || l.country || l.region || l.city
FROM blocks b
JOIN locations l ON (b.location_id = l.id)
WHERE $1 >= start_ip
and $1 <= end_ip
limit 1;
Can you provide the table definitions for blocks and locations,
including indexes? Also, could you tell us what the OS is, how
much RAM is on the system, what the storage system looks like, and
provide the output from running this?:
SELECT version();
SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');
You might also try running EXPLAIN ANALYZE for this query after
running these statements on the connection, and see if you get a
different plan:
VACUUM ANALYZE blocks;
VACUUM ANALYZE locations;
SET cpu_tuple_cost = 0.03;
SET random_page_cost = 1;
SET effective_cache_size = <75% of machine RAM>
SET work_mem = <25% of machine RAM / max_connections>
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
