Thank you for the pointers. I will try those suggestions. As I mentioned later, resolving the query solved the problem for now.
Regards,On Thu, Sep 19, 2013 at 11:40 AM, Kevin Grittner-5 [via PostgreSQL] <[hidden email]> wrote:
Jayadevan <[hidden email]> 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.025 rows=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.268 rows=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>
Sent via pgsql-general mailing list ([hidden email])
http://postgresql.1045698.n5.nabble.com/Query-CPU-issue-tp5771421p5771567.htmlIf you reply to this email, your message will be added to the discussion below:
View this message in context: Re: Query - CPU issue
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
