Search Postgresql Archives

Re: [PERFORM] Very slow inner join query Unacceptable latency.

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

 



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.

But what negative impact is disabling hash joins?

Sorry, I just executed the explain without the analyze, I'll send out the "explain analyze" next reply.

thanks

Freddie

-------- Original Message --------
Subject: Re: [PERFORM] Very slow inner join query Unacceptable latency.
From: Jeff Janes <jeff.janes@xxxxxxxxx>
Date: Wed, May 22, 2013 5:17 pm
To: fburgess@xxxxxxxxxxxxxxx
Cc: Jaime Casanova <jaime@xxxxxxxxxxxxxxx>, psql performance list
<pgsql-performance@xxxxxxxxxxxxxx>, Postgres General
<pgsql-general@xxxxxxxxxxxxxx>

On Wed, May 22, 2013 at 7:41 AM, <fburgess@xxxxxxxxxxxxxxx> 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
 

[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