Search Postgresql Archives

Re: Query - CPU issue

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thank you for the pointers. I will try those suggestions. As I mentioned later, resolving the query solved the problem for now.
Regards,
Jayadevan


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>

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Query-CPU-issue-tp5771421p5771567.html
To unsubscribe from Query - CPU issue, click here.
NAML



View this message in context: Re: Query - CPU issue
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux