I have created a table with two columns and created index for the first column. Then inserted 2 million data using generate series function for first column (2nd column is having null). And executed below query

    explain select * from mytable;

Getting cost as 19116.00 (something like this)

Then I am removing 1 million record from the mytable and running the explain query again, but getting more cost

Enabled vacuum parameters

track_counts = on
autovacuum = on
log_autovacuum_min_duration = 0

I could not understand, why cost is getting increased (expecting less cost). Is there any other parameter needs to be enabled?

(Using Postgres 9.1)

Edit 1

create table mytable (c1 numeric(19,0), c2 numeric(19,0));

explain analyze select * from mytable;

"Seq Scan on mytable  (cost=0.00..21.60 rows=1160 width=40) (actual time=0.002..0.002 rows=0 loops=1)"

"Total runtime: 0.033 ms"

create index c1_idx on mytable using btree (c1);

insert into mytable (c1) values (generate_series(1,2000000))

explain analyze select * from mytable;

"Seq Scan on mytable  (cost=0.00..19116.00 rows=1026600 width=40) (actual time=0.048..251.834 rows=2000000 loops=1)"

"Total runtime: 293.915 ms"

select pg_size_pretty(pg_table_size('mytable')) --"69 MB"

delete from mytable where c1 between 1 and 1000000

explain analyze  select * from mytable;

"Seq Scan on mytable  (cost=0.00..28850.00 rows=2000000 width=26) (actual time=356.616..458.278 rows=1000000 loops=1)"

"Total runtime: 478.645 ms" 

(Actually, cost increased and still table size is same)

Edit 2

After runs vacuum on the table

"Seq Scan on mytable  (cost=0.00..18850.00 rows=1000000 width=26) (actual time=31.308..117.244 rows=1000000 loops=1)"

"Total runtime: 138.911 ms"

Cost reduced as expected, but got another doubt on it. When mytable is having only 1M record cost is less than above.

truncate mytable;

insert into mytable (c1) values (generate_series(1000001,2000000))

explain analyze select * from mytable;

"Seq Scan on mytable  (cost=0.00..10841.25 rows=641625 width=26) (actual time=0.058..121.894 rows=1000000 loops=1)"

"Total runtime: 154.454 ms"

Old cost is around 18K where for the same rows around 10K. Kindly ignore if its changing the topic :-)

  • Why do you think the cost should get smaller when you run vacuum? You should show us the complete execution plan (maybe even with explain analyze) – a_horse_with_no_name Mar 16 '16 at 10:04
  • as per my understanding, auto-vacuum does dead tuples removal and index recreation. correct me if my understanding is wrong. i will share explain analyze result shortly. – Spike Mar 16 '16 at 10:29
  • 1
    autovacuum never kicked in your example. It doesn't run immediately after a (bulk) delete - it runs periodically and checks if any tables need vacuuming. If you run vacuum mytable after deleting the rows you should see a difference in the cost and runtime. If you run explain (analyze, verbose, buffers) before running vacuum you will probably see that Postgres reads too many blocks after the bulk delete. – a_horse_with_no_name Mar 16 '16 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.