Re: Seeking help with a query that takes too long
От | Nick Fankhauser |
---|---|
Тема | Re: Seeking help with a query that takes too long |
Дата | |
Msg-id | NEBBLAAHGLEEPCGOBHDGAEKAJGAA.nickf@ontko.com обсуждение исходный текст |
Ответ на | Re: Seeking help with a query that takes too long (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Seeking help with a query that takes too long
|
Список | pgsql-performance |
> 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
В списке pgsql-performance по дате отправления: