Re: Seeking help with a query that takes too long

> 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