Re: Very slow inner join query Unacceptable latency.

[<fburgess@xxxxxxxxxxxxxxx>]

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