Saturday, June 16, 2012

PostgreSQL Tidbits

Couple things here.

1. Counting records in a postgres table is slow!
SELECT count(*) FROM foo; is slow as molasses…a good alternative is:
SELECT reltuples FROM pg_class WHERE relname = 'foo';
Be careful through, this method relies on the statistics built when running ANALYZE. So if your table is static, good, else make sure you keep up with analyze if using this method.

2. Adding a serial column in a postgres table is slow!
If you need a unique id, adding a serial column will do the job and will update if you insert new records. But this is an extremely slow process. If you need a unique id and know your table will be static, you can use:
ROW_NUMBER() OVER() AS unique_id when generating your table, and it is relatively fast.
This method can also be used when generating a view to be visualized in QGIS. If you have ever tried to add a view without a unique id in QGIS, you'll get an error. To get around this, use the method above to create the unique id on the fly.