Re: Query - CPU issue

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] <
ml-node+s1045698n5771567h91(at)n5(dot)nabble(dot)com> wrote:

> Jayadevan <[hidden email]<http://user/SendEmail.jtp?type=node&node=5771567&i=0>>
> 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]<http://user/SendEmail.jtp?type=node&node=5771567&i=1>)
>
> 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<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5771421&code=bWF5bWFsYS5qYXlhZGV2YW5AZ21haWwuY29tfDU3NzE0MjF8LTE0MDY3ODcxNjA=>
> .
> NAML<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Query-CPU-issue-tp5771421p5771569.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.