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+s1045698n5771567...@n5.nabble.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.
