Postgresql - measuring performance and how to tell whether indexes should be refreshed

Question

For background of this please see: Do I need a primary key for my table, which has a UNIQUE (composite 4-columns), one of which can be NULL?

My questions are quite simple:

I have a table that holds product pricing information, synchronised from another database. There is just one field in this table that is controlled by the client, and the rest is dropped and re-inserted on synchronisation every once in a while (e.g. once a day to once a week, manually run by the client using a PHP script).

1) The table has an index on 4 columns (one of which can be null) and another partial index on the 3 not-null columns. When you drop about 90% of your table and re-insert data, is it good to also drop your indexes and re-create them after all the data is in the table, or is it better to simply keep the indexes "as is"?

I have now switched to another approach suggested by Erwin Brandstetter:

CREATE TEMP TABLE pr_tmp AS
SELECT * FROM product_pricebands WHERE my_custom_field IS TRUE;
TRUNCATE product_pricebands;
INSERT INTO product_pricebands SELECT * FROM pr_tmp;

which seems to be working very well, so I'm not sure if I need to drop and recreate my indexes or why I would need to do this. Any suggestions?

2) Also, how do I measure performance of my script? I actually want to know if:

CREATE TEMP TABLE pr_tmp AS
SELECT * FROM product_pricebands WHERE my_custom_field IS TRUE;
TRUNCATE product_pricebands;
INSERT INTO product_pricebands SELECT * FROM pr_tmp;

has better performance than

DELETE FROM product_pricebands WHERE my_custom_field IS TRUE;

Can I tell this via PHP? I tried EXPLAIN ANALYZE but I'm not sure if that works for a group of statements like the above?

Many thanks!

"*how do I measure performance of my script*": take the time before running it, take the time after running. The smaller the difference the better the statement. — , May 10 '12 at 14:55
You mean `DELETE FROM product_pricebands WHERE my_custom_field IS **FALSE**;` — Clodoaldo Neto, May 11 '12 at 12:10

score 2 · Answer 1 · answered May 11 '12 at 09:21

If your Postgres version is >= 8.4, you can use auto explain module: http://www.postgresql.org/docs/8.4/static/auto-explain.html

You can also use in your script: SET log_min_duration_statement = 0; SET log_duration = on; to log all statements and timeings for this script.

Clodoaldo Neto · Answer 2 · 2012-05-11T12:27:05.690

As for number one I don't know if it is important to rebuild the indexes but I think that if the table is big than dropping and recreating the indexes can be a performance improvement:

CREATE TEMP TABLE pr_tmp AS
    SELECT * 
    FROM product_pricebands 
    WHERE my_custom_field IS TRUE
-- drop all indexes here
TRUNCATE product_pricebands;
INSERT INTO product_pricebands 
    SELECT * 
    FROM pr_tmp;
-- recreate them here

Postgresql - measuring performance and how to tell whether indexes should be refreshed

2 Answers2