Very slow inner join query Unacceptable latency.
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>The SARS_ACTS table currently has 37,115,515 rows<br><br>we have indexed: idx_sars_acts_acts_run_id ON SARS_ACTS USING btree (sars_run_id)<br>we have pk constraint on the SARS_ACTS_RUN table; sars_acts_run_pkey PRIMARY KEY (id )<br><br>serverdb=# explain select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a> where tr1_.ALGORITHM='SMAT';<br> QUERY PLAN<br>--------------------------------------------------------------------------------------------------------------------------<br>Aggregate (cost=4213952.17..4213952.18 rows=1 width=0)<br> -> Hash Join (cost=230573.06..4213943.93 rows=3296 width=0)<br> Hash Cond: (this_.SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a>)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -> Seq Scan om sars_acts this_ (cost=0.00..3844241.84 rows=37092284 width=8)<br> -> Hash (cost=230565.81..230565.81 rows=580 width=8)<br> -> Seq Scan on sars_acts_run tr1_ (cost=0.00..230565.81 rows=580 width=8)<br> Filter: ((algorithm)::text = 'SMAT'::text)<br>(7 rows)<br><br>This query executes in approximately 5.3 minutes to complete, very very slow, our users are not happy.<br><br>I did add an index on SARS_ACTS_RUN.ALGORITHM column but it didn't improve the run time. <br>The planner just changed the "Filter:" to an "Index Scan:" improving the cost of the Seq Scan <br>on the sars_acts_run table, but the overall run time remained the same. It seems like the bottleneck <br>is in the Seq Scan on the sars_acts table.<br><br> -> Seq Scan on sars_acts_run tr1_ (cost=0.00..230565.81 rows=580 width=8)<br> Filter: ((algorithm)::text = 'SMAT'::text)<br><br>Does anyone have suggestions about how to speed it up?<br><a target="_blank" href="mailto:pgsql-performance@postgresql.org"></a></div></span></body></html>
On Tue, May 21, 2013 at 4:53 PM, <fburgess@radiantblue.com> wrote:
The SARS_ACTS table currently has 37,115,515 rows
we have indexed: idx_sars_acts_acts_run_id ON SARS_ACTS USING btree
(sars_run_id)
we have pk constraint on the SARS_ACTS_RUN table; sars_acts_run_pkey PRIMARY
KEY (id )serverdb=# explain select count(*) as y0_ from SARS_ACTS this_ inner join
SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.ID where tr1_.ALGORITHM='SMAT';
can you please show us an EXPLAIN ANALYZE of this query (not only
EXPLAIN). please paste it in a file and attach it so it doesn't get
reformatted by the mail client.
what version of postgres is this?
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On Wednesday, May 22, 2013 3:24 AM fburgess wrote:
The SARS_ACTS table currently has 37,115,515 rows
we have indexed: idx_sars_acts_acts_run_id ON SARS_ACTS USING btree (sars_run_id)
we have pk constraint on the SARS_ACTS_RUN table; sars_acts_run_pkey PRIMARY KEY (id )
serverdb=# explain select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.ID where tr1_.ALGORITHM='SMAT';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4213952.17..4213952.18 rows=1 width=0)
-> Hash Join (cost=230573.06..4213943.93 rows=3296 width=0)
Hash Cond: (this_.SARS_RUN_ID=tr1_.ID)
-> Seq Scan om sars_acts this_ (cost=0.00..3844241.84 rows=37092284 width=8)
-> Hash (cost=230565.81..230565.81 rows=580 width=8)
-> Seq Scan on sars_acts_run tr1_ (cost=0.00..230565.81 rows=580 width=8)
Filter: ((algorithm)::text = 'SMAT'::text)
(7 rows)
This query executes in approximately 5.3 minutes to complete, very very slow, our users are not happy.
I did add an index on SARS_ACTS_RUN.ALGORITHM column but it didn't improve the run time.
The planner just changed the "Filter:" to an "Index Scan:" improving the cost of the Seq Scan
on the sars_acts_run table, but the overall run time remained the same. It seems like the bottleneck
is in the Seq Scan on the sars_acts table.
-> Seq Scan on sars_acts_run tr1_ (cost=0.00..230565.81 rows=580 width=8)
Filter: ((algorithm)::text = 'SMAT'::text)
Does anyone have suggestions about how to speed it up?
Could you please once trying Analyzing both tables and then run the query to check which plan it uses:
Analyze SARS_ACTS;
Analyze SARS_ACTS_RUN;
With Regards,
Amit Kapila.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
The SARS_ACTS table currently has 37,115,515 rows
I re-added the index on algorithm: idx_sars_acts_run_algorithm ON SARS_ACTS_RUN (algorithm)
serverdb=# explain analyze select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.ID where tr1_.ALGORITHM='SMAT';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3983424.05..3983424.06 rows=1 width=0) (actual time=1358298.003..1358298.004 rows=1 loops=1)
-> Hash Join (cost=44.93..3983415.81 rows=3297 width=0) (actual time=2593.768..1358041.205 rows 1481710 loops=1)
Hash Cond: (this_.SARS_RUN_ID=tr1_.ID)
-> Seq Scan om sars_acts this_ (cost=0.00..3844241.84 rows=37092284 width=8) (actual time=0.026..1348954.763 rows=37461345 loops=1)
-> Hash (cost=37.68..37.68 rows=580 width=8) (actual time=435.655..435.655 rows=441 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 18kB
-> Index Scan using idx_mars_track_run_algorithm on SARS_ACTS_RUN tr1_ (cost=0.00..37.68 rows=580 width=8) (actual time=10.580..435.273 rows 441 loops=1)
Index Cond: ((algorithm)::text = 'SMAT'::text)
Total runtime: 1358298.664 ms <- 22.6383 minutes
(9 rows)
Attachments:
explainanalyzesql.txttext/plain; name=explainanalyzesql.txtDownload
Import Notes
Resolved by subject fallback
On Wed, May 22, 2013 at 7:41 AM, <fburgess@radiantblue.com> wrote:
PostgreSQL 9.1.6 on linux
From the numbers in your attached plan, it seems like it should be doing a
nested loop from the 580 rows (it thinks) that match in SARS_ACTS_RUN
against the index on sars_run_id to pull out the 3297 rows (again, it
think, though it is way of there). I can't see why it would not do that.
There were some planner issues in the early 9.2 releases that caused very
large indexes to be punished, but I don't think those were in 9.1
Could you "set enable_hashjoin to off" and post the "explain analyze" that
that gives?
Cheers,
Jeff
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>serverdb=# set enable_hashjoin=off;<br>SET<br>serverdb=# explain select count(*) as y0_ from SARS_ACTS this_ inner join <span style="color:rgb(0,0,0);white-space:pre-wrap">SARS_ACTS_RUN</span> tr1_ on this_.SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a> where tr1.ALGORITHM='SMAT';<br><br> QUERY PLAN<br>--------------------------------------------------------------------------------------------------------------------------<br>Aggregate (cost=7765563.69..7765563.70 rows=1 width=0) <br> Nested Loop (cost=0.00..7765555.35 rows=3336 width=0)<br> -> Index Scan using idx_sars_acts_run_algorithm on sars_acts_run tr1_ (cost=0.00..44.32 rows=650 width=8) <br> Index Cond: ((algorithm)::text = 'SMAT'::text)<br> -> Index Scan using idx_sars_acts_run_id_end_time on sars_acts this_ (cost=0.00..11891.29 rows=4452 width=8) <br> Index Cond: (SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a>)<br>(6 rows)<br><br>serverdb=# \timing<br>TIming is on.<br><br>serverdb=# select count(*) as y0_ from SARS_ACTS this_ inner join <span style="color:rgb(0,0,0);white-space:pre-wrap">SARS_ACTS_RUN</span> tr1_ on this_.SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a> where tr1.ALGORITHM='SMAT';<br> y0_<br>------<br>1481710<br>(1 row)<br><br>Time: 85069.416 ms < 1.4 minutes <-- not great, but much better!<br><br>Subsequently, runs in the milliseconds once cached.</div><div><br></div><div>But what negative impact is disabling hash joins?</div><div><br></div><div>Sorry, I just executed the explain without the analyze, I'll send out the "explain analyze" next reply.</div><div><br></div><div>thanks</div><div><br></div><div>Freddie<br></div><div><br></div>
<blockquote id="replyBlockquote" webmail="1" style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verdana;">
<div id="wmQuoteWrapper">
-------- Original Message --------<br>
Subject: Re: [PERFORM] Very slow inner join query Unacceptable latency.<br>
From: Jeff Janes <<a href="mailto:jeff.janes@gmail.com">jeff.janes@gmail.com</a>><br>
Date: Wed, May 22, 2013 5:17 pm<br>
To: <a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a><br>
Cc: Jaime Casanova <<a href="mailto:jaime@2ndquadrant.com">jaime@2ndquadrant.com</a>>, psql performance list<br>
<<a href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>>, Postgres General<br>
<<a href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>><br>
<br>
<div dir="ltr">On Wed, May 22, 2013 at 7:41 AM, <span dir="ltr"><<a href="mailto:fburgess@radiantblue.com" target="_blank">fburgess@radiantblue.com</a>></span> wrote:<br><div class="gmail_extra"><div class="gmail_quote"> <blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div><span style="font-size:10pt;font-family:Verdana"><div> PostgreSQL 9.1.6 on linux<br></div></span></div></blockquote><div><br></div><div><br></div><div style="">>From the numbers in your attached plan, it seems like it should be doing a nested loop from the 580 rows (it thinks) that match in <span style="color:rgb(0,0,0);white-space:pre-wrap">SARS_ACTS_RUN against the index on sars_run_id to pull out the </span><span style="color:rgb(0,0,0);white-space:pre-wrap">3297 rows (again, it think, though it is way of there)</span><span style="color:rgb(0,0,0);white-space:pre-wrap">. I can't see why it would not do that. There were some planner issues in the early 9.2 releases that caused very large indexes to be punished, but I don't think those were in 9.1</span></div> <div style=""><span style="color:rgb(0,0,0);white-space:pre-wrap"><br></span></div><div style=""><font color="#000000"><span style="white-space:pre-wrap">Could you "set enable_hashjoin to off" and post the "explain analyze" that that gives?</span></font><br> </div><div style=""><font color="#000000"><span style="white-space:pre-wrap"><br></span></font></div><div style=""><font color="#000000"><span style="white-space:pre-wrap"><br></span></font></div><div style=""><font color="#000000"><span style="white-space:pre-wrap">Cheers,</span></font></div> <div style=""><font color="#000000"><span style="white-space:pre-wrap"><br></span></font></div><div style=""><font color="#000000"><span style="white-space:pre-wrap">Jeff</span></font></div><div> </div></div></div></div>
</div>
</blockquote></span></body></html>
Import Notes
Resolved by subject fallback
On Thu, May 23, 2013 at 12:21 PM, <fburgess@radiantblue.com> wrote:
But what negative impact is disabling hash joins?
doing it just for a single query, could be a tool for solving
particular problems.
setting it in postgresql.conf, therefore affecting all queries, is
like using a hammer to change tv channel... it will cause more
problems than the one it solves.
what you can do is:
1) execute:
SET enable_hashjoin TO OFF;
SELECT here
RESET enable_hashjoin TO ON;
2) in a function:
CREATE FUNCTION do_something() RETURNS bigint AS
$$
SELECT here
$$ LANGUAGE sql SET enable_hashjoin TO OFF STABLE;
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thursday, May 23, 2013 10:51 PM fburgess wrote:
serverdb=# set enable_hashjoin=off;
SET
serverdb=# explain select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.ID where tr1.ALGORITHM='SMAT';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=7765563.69..7765563.70 rows=1 width=0)
Nested Loop (cost=0.00..7765555.35 rows=3336 width=0)
-> Index Scan using idx_sars_acts_run_algorithm on sars_acts_run tr1_ (cost=0.00..44.32 rows=650 width=8)
Index Cond: ((algorithm)::text = 'SMAT'::text)
-> Index Scan using idx_sars_acts_run_id_end_time on sars_acts this_ (cost=0.00..11891.29 rows=4452 width=8)
Index Cond: (SARS_RUN_ID=tr1_.ID)
(6 rows)
serverdb=# \timing
TIming is on.
serverdb=# select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.ID where tr1.ALGORITHM='SMAT';
y0_
------
1481710
(1 row)
Time: 85069.416 ms < 1.4 minutes <-- not great, but much better!
Subsequently, runs in the milliseconds once cached.
If I see the plan from your other mail as below where Hash join is selected, the cost of Nested Loop is much more, that is the reason why optimizer would have selected
Hash Join.
serverdb=# explain analyze select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.ID where tr1_.ALGORITHM='SMAT';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3983424.05..3983424.06 rows=1 width=0) (actual time=1358298.003..1358298.004 rows=1 loops=1)
-> Hash Join (cost=44.93..3983415.81 rows=3297 width=0) (actual time=2593.768..1358041.205 rows 1481710 loops=1)
It is quite surprising that after optimizer decided the cost of some plan (Hash Join) to be lower but actual execution cost of same is more.
There might be some problem with cost calculation model of Hash Join for some cases.
By the way which version of PostgreSQL you are using?
But what negative impact is disabling hash joins?
I think using it as a temporary fix might be okay, but keeping such code in your application might be risky for you, because as the data changes in your tables, it could be quite possible that
in future Hash Join might be the best and cheapest way.
Can you try reproducing it with small data or else can you attach your schema and data for the tables/indexes used in query?
With Regards,
Amit Kapila.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Looking at the execution plan makes me wonder what your work_mem is
set to. Try cranking it up to test and lowering random_page_cost:
set work_mem='500MB';
set random_page_cost=1.2;
explain analyze select ...
and see what you get.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div><br>1.) Server setting<br><br>memory: 32960116kB = 32GB<br><br><br>2.) Current Postgresql configuration settings of note in my environment.<br><br>enable_hashjoin=off<br>work_mem = 16MB <br>#random_page_cost-4.0 <- default<br>maintenance_work_mem=256MB<br>shared_buffers = 8GB<br><br><br>serverdb=# explain analyze select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a> where tr1.ALGORITHM='SMAT';<br><br> QUERY PLAN<br>--------------------------------------------------------------------------------------------------------------------------<br>Aggregate (cost=5714258.72..5714258.73 rows=1 width=0) (actual time=54402.148..54402.148 rows=1 loops=1)<br> Nested Loop (cost=0.00..5714253.25 rows=2188 width=0) (actual time=5.920..54090.676 rows=1481710 loops=1)<br> -> Index Scan using idx_SARS_ACTS_run_algorithm on SARS_ACTS_run tr1_ (cost=0.00..32.71 rows=442 width=8) (actual time=1.423..205.256 rows=441 loops=1)<br> Index Cond: ((algorithm)::text = 'SMAT'::text)<br> -> Index Scan using idx_SARS_ACTS_run_id_end_time on SARS_ACTS this_ (cost=0.00..12874.40 rows=4296 width=8) (actual time=749..121.125 rows=3360 loops=441)<br> Index Cond: (SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a>)<br>Total runtime: 54402.212 ms <- 54 seconds<br><br>(7 rows)<br><br>3.) Setting the recommended parameters<br><br><br>serverdb=# set work_mem='500MB';<br>SET<br>serverdb=# set random_page_cost=1.2;<br>SET<br>serverdb=# explain analyze select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a> where tr1.ALGORITHM='SMAT';<br><br> QUERY PLAN<br>--------------------------------------------------------------------------------------------------------------------------<br>Aggregate (cost=1754246.85..1754246.86 rows=1 width=0) (actual time=1817.644..1817.644 rows=1 loops=1)<br> Nested Loop (cost=0.00..1754241.38 rows=2188 width=0) (actual time=0.135..1627.954 rows=1481710 loops=1)<br> -> Index Scan using idx_SARS_ACTS_run_algorithm on SARS_ACTS_run tr1_ (cost=0.00..22.40 rows=442 width=8) (actual time=0.067..0.561 rows=441 loops=1)<br> Index Cond: ((algorithm)::text = 'SMAT'::text)<br> -> Index Scan using idx_SARS_ACTS_run_id_end_time on SARS_ACTS this_ (cost=0.00..3915.12 rows=4296 width=8) (actual time=0.008..2.972 rows=3360 loops=441)<br> Index Cond: (SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a>)<br>Total runtime: 1817.695 ms 1.8 seconds <- very good response time improvement<br><br>(7 rows)<br><br>4.) Now toggling the enable_hashjoin, I suspect the plan is cached, so these results may be suspect.<br><br><br>serverdb=# set enable_hashjoin=on;<br>SET<br>serverdb=# explain analyze select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a> where tr1.ALGORITHM='SMAT';<br><br> QUERY PLAN<br>--------------------------------------------------------------------------------------------------------------------------<br>Aggregate (cost=1754246.85..1754246.86 rows=1 width=0) (actual time=1606.683..1606.683 rows=1 loops=1)<br> Nested Loop (cost=0.00..1754241.38 rows=2188 width=0) (actual time=0.136..1442.463 rows=1481710 loops=1)<br> -> Index Scan using idx_SARS_ACTS_run_algorithm on SARS_ACTS_run tr1_ (cost=0.00..22.40 rows=442 width=8) (actual time=0.068..0.591 rows=441 loops=1)<br> Index Cond: ((algorithm)::text = 'SMAT'::text)<br> -> Index Scan using idx_SARS_ACTS_run_id_end_time on SARS_ACTS this_ (cost=0.00..3915.12 rows=4296 width=8) (actual time=0.007..2.659 rows=3360 loops=441)<br> Index Cond: (SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a>)<br>Total runtime: 1606.728 ms 1.6 seconds <- very good response time improvement<br><br>(7 rows)<br><br>Questions:<br><br>Any concerns with setting these conf variables you recommended; work_mem, random_page_cost dbserver wide (in postgresql,conf)? <br><br>Thanks so much!!!<br><br></div>
<blockquote id="replyBlockquote" webmail="1" style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verdana;">
<div id="wmQuoteWrapper">
-------- Original Message --------<br>
Subject: Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable<br>
latency.<br>
From: Scott Marlowe <<a href="mailto:scott.marlowe@gmail.com">scott.marlowe@gmail.com</a>><br>
Date: Thu, May 23, 2013 11:16 pm<br>
To: <a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a><br>
Cc: Jaime Casanova <<a href="mailto:jaime@2ndquadrant.com">jaime@2ndquadrant.com</a>>, psql performance list<br>
<<a href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>>, Postgres General<br>
<<a href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>><br>
<br>
Looking at the execution plan makes me wonder what your work_mem is<br>
set to. Try cranking it up to test and lowering random_page_cost:<br>
<br>
set work_mem='500MB';<br>
set random_page_cost=1.2;<br>
explain analyze select ...<br>
<br>
and see what you get.<br>
</div>
</blockquote></span></body></html>
Import Notes
Resolved by subject fallback
On Fri, May 24, 2013 at 3:44 PM, <fburgess@radiantblue.com> wrote:
Total runtime: 1606.728 ms 1.6 seconds <- very good response time
improvement(7 rows)
Questions:
Any concerns with setting these conf variables you recommended; work_mem,
random_page_cost dbserver wide (in postgresql,conf)?Thanks so much!!!
Yes 500MB is pretty high especially if you have a lot of connections.
Try it with it back down to 16MB and see how it does. Work mem is per
sort so a setting as high as 500MB can exhaust memory on the machine
under heavy load.
--
To understand recursion, one must first understand recursion.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>We re-tested these settings a few times after our initial test and realized that the execution time I posted was shewed, because the execution plan was cached after the initial run. Subsequent executions ran in a little over a second.</div>
<div>There ended up being no significant saving by setting these parameters. Un-cached the query ran in about 55 seconds. </div>
<div> </div>
<BLOCKQUOTE style="BORDER-LEFT: blue 2px solid; PADDING-LEFT: 8px; FONT-FAMILY: verdana; COLOR: black; MARGIN-LEFT: 8px; FONT-SIZE: 10pt" id=replyBlockquote webmail="1">
<DIV id=wmQuoteWrapper>-------- Original Message --------<BR>Subject: Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable<BR>latency.<BR>From: Scott Marlowe <<a href="mailto:scott.marlowe@gmail.com">scott.marlowe@gmail.com</a>><BR>Date: Fri, May 24, 2013 3:03 pm<BR>To: <a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a><BR>Cc: Jaime Casanova <<a href="mailto:jaime@2ndquadrant.com">jaime@2ndquadrant.com</a>>, psql performance list<BR><<a href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>>, Postgres General<BR><<a href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>><BR><BR>On Fri, May 24, 2013 at 3:44 PM, <<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>> wrote:<BR><BR>> Total runtime: 1606.728 ms 1.6 seconds <- very good response time<BR>> improvement<BR>><BR>> (7 rows)<BR>><BR>> Questions:<BR>><BR>> Any concerns with setting these conf variables you recommended; work_mem,<BR>> random_page_cost dbserver wide (in postgresql,conf)?<BR>><BR>> Thanks so much!!!<BR><BR>Yes 500MB is pretty high especially if you have a lot of connections.<BR>Try it with it back down to 16MB and see how it does. Work mem is per<BR>sort so a setting as high as 500MB can exhaust memory on the machine<BR>under heavy load.<BR><BR>--<BR>To understand recursion, one must first understand recursion.<BR></DIV></BLOCKQUOTE></span></body></html>
Import Notes
Resolved by subject fallback