Re: Query - CPU issue - Mailing list pgsql-general

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