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
copy link · copy postgr.es
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