Re: Delete query takes exorbitant amount of time
От | Karim Nassar |
---|---|
Тема | Re: Delete query takes exorbitant amount of time |
Дата | |
Msg-id | 1111884288.27481.6.camel@k2.cet.nau.edu обсуждение исходный текст |
Ответ на | Re: Delete query takes exorbitant amount of time (Stephan Szabo <sszabo@megazone.bigpanda.com>) |
Ответы |
Re: Delete query takes exorbitant amount of time
(Stephan Szabo <sszabo@megazone.bigpanda.com>)
|
Список | pgsql-performance |
On Sat, 2005-03-26 at 15:18 -0800, Stephan Szabo wrote: > On Sat, 26 Mar 2005, Karim Nassar wrote: > > > On Sat, 2005-03-26 at 07:55 -0800, Stephan Szabo wrote: > > > That seems like it should be okay, hmm, what does something like: > > > > > > PREPARE test(int) AS SELECT 1 from measurement where > > > id_int_sensor_meas_type = $1 FOR UPDATE; > > > EXPLAIN ANALYZE EXECUTE TEST(1); > > > > > > give you as the plan? > > > > QUERY PLAN > > ----------------------------------------------------------------------------------------------------------------------- > > Seq Scan on measurement (cost=0.00..164559.16 rows=509478 width=6) > > (actual time=11608.402..11608.402 rows=0 loops=1) > > Filter: (id_int_sensor_meas_type = $1) > > Total runtime: 11608.441 ms > > (3 rows) > > Hmm, has measurement been analyzed recently? You might want to see if > raising the statistics target on measurement.id_int_sensor_meas_type and > reanalyzing changes the estimated rows down from 500k. orfs=# ALTER TABLE measurement ALTER COLUMN id_int_sensor_meas_type SET STATISTICS 1000; ALTER TABLE orfs=# VACUUM FULL ANALYZE VERBOSE; <snip> INFO: free space map: 52 relations, 13501 pages stored; 9760 total pages needed DETAIL: Allocated FSM size: 1000 relations + 300000 pages = 1864 kB shared memory. VACUUM orfs=# PREPARE test(int) AS SELECT 1 from measurement where orfs-# id_int_sensor_meas_type = $1 FOR UPDATE; PREPARE orfs=# EXPLAIN ANALYZE EXECUTE TEST(1); QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Seq Scan on measurement (cost=0.00..164559.16 rows=509478 width=6) (actual time=8948.452..8948.452 rows=0 loops=1) Filter: (id_int_sensor_meas_type = $1) Total runtime: 8948.494 ms (3 rows) orfs=# EXPLAIN ANALYZE EXECUTE TEST(1); QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Seq Scan on measurement (cost=0.00..164559.16 rows=509478 width=6) (actual time=3956.616..3956.616 rows=0 loops=1) Filter: (id_int_sensor_meas_type = $1) Total runtime: 3956.662 ms (3 rows) Some improvement. Even better once it's cached. Row estimate didn't change. Is this the best I can expect? Is there any other optimizations I am missing? TIA, -- Karim Nassar Department of Computer Science Box 15600, College of Engineering and Natural Sciences Northern Arizona University, Flagstaff, Arizona 86011 Office: (928) 523-5868 -=- Mobile: (928) 699-9221
В списке pgsql-performance по дате отправления: