pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Nick Fankhauser <nickf@ontko.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Manfred Koizar <mkoi-pg@aon.at>
Cc: Pgsql-Performance@Postgresql. Org <pgsql-performance@postgresql.org>
Subject: Re: Seeking help with a query that takes too long
Date: Wed, 12 Nov 2003 13:27:53 -0500
Message-ID: <NEBBLAAHGLEEPCGOBHDGAEKAJGAA.nickf@ontko.com> (raw)
In-Reply-To: <10144.1068657035@sss.pgh.pa.us>


> You might have to resort to brute force, like "set enable_nestloop=false".
> Just out of curiosity, what do you get if you do that?

I get a different plan, but similar execution time:


 Limit  (cost=323437.13..323437.13 rows=1 width=115) (actual
time=170921.89..170922.95 rows=1000 loops=1)
   ->  Sort  (cost=323437.13..323437.13 rows=1 width=115) (actual
time=170921.89..170922.26 rows=1001 loops=1)
         Sort Key: max((actor.actor_full_name)::text),
count(case_data.case_id)
         ->  Aggregate  (cost=323437.08..323437.12 rows=1 width=115) (actual
time=170849.94..170898.06 rows=3457 loops=1)
               ->  Group  (cost=323437.08..323437.09 rows=3 width=115)
(actual time=170849.90..170873.60 rows=5880 loops=1)
                     ->  Sort  (cost=323437.08..323437.08 rows=3 width=115)
(actual time=170847.97..170850.21 rows=5880 loops=1)
                           Sort Key: actor.actor_id
                           ->  Hash Join  (cost=253333.29..323437.06 rows=3
width=115) (actual time=122873.80..170814.27 rows=5880 loops=1)
                                 Hash Cond: ("outer".case_id =
"inner".case_id)
                                 ->  Seq Scan on case_data
(cost=0.00..60368.16 rows=1947116 width=39) (actual time=12.95..43542.25
rows=1947377 loops=1)
                                 ->  Hash  (cost=253333.28..253333.28 rows=3
width=76) (actual time=122844.40..122844.40 rows=0 loops=1)
                                       ->  Hash Join  (cost=6.02..253333.28
rows=3 width=76) (actual time=24992.70..122810.32 rows=5883 loops=1)
                                             Hash Cond: ("outer".actor_id =
"inner".actor_id)
                                             ->  Seq Scan on
actor_case_assignment  (cost=0.00..209980.49 rows=8669349 width=34) (actual
time=9.13..85504.05 rows=8670467 loops=1)
                                             ->  Hash  (cost=6.01..6.01
rows=1 width=42) (actual time=24926.56..24926.56 rows=0 loops=1)
                                                   ->  Index Scan using
actor_full_name_uppercase on actor  (cost=0.00..6.01 rows=1 width=42)
(actual time=51.67..24900.53 rows=3502 loops=1)
                                                         Index Cond:
((actor_full_name_uppercase >= 'SANDERS'::character varying) AND
(actor_full_name_uppercase < 'SANDERT'::character varying))
                                                         Filter:
(actor_full_name_uppercase ~~ 'SANDERS%'::text)
 Total runtime: 170925.93 msec
(19 rows)


-Nick





view thread (11+ messages)  latest in thread

Message-ID: <NEBBLAAHGLEEPCGOBHDGAEKAJGAA.nickf@ontko.com>
Permalink:  ../NEBBLAAHGLEEPCGOBHDGAEKAJGAA.nickf@ontko.com/
Also on:    postgresql.org/message-id/NEBBLAAHGLEEPCGOBHDGAEKAJGAA.nickf@ontko.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: nickf@ontko.com, tgl@sss.pgh.pa.us, mkoi-pg@aon.at
  Subject: Re: Seeking help with a query that takes too long
  In-Reply-To: <NEBBLAAHGLEEPCGOBHDGAEKAJGAA.nickf@ontko.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox