Re: Tweaking PG (again)

On Fri, Nov 14, 2008 at 12:57:32PM +0800, Phoenix Kiula wrote:
> 1. If I have a unique index on (user_id, url_encrypted), then will
> queries asking only for user_id also use this index? Or should i
> simply have separate indexes on user_id and url_encrypted? I vaguely
> recall reading somewhere that compound indexes may have been useful in
> MySQL but according to PG's more advanced planner having two separate
> indexes on the columns works better.

Yes. Maybe. If you build a combined index (user_id, url_encrypted) then
it can't be used in query that only look for url_encrypted. So it
depends on your queries. If you want to be able to search for
url_encrypted by itself sometimes, it might be an idea to have two
indexes.

> 2. Is there a production equivalent of REINDEX? Last time I tried
> CREATE INDEX CONCURRENTLY overnight, by the morning it had croaked
> with these errors:

Sorry, can't help you here...

> 3. Basically, design wise, I use url_encrypted to check if a user_id
> already has a url associated with him. This kind of a unique
> constraint check (user_id, url_encrypted). Used only when INSERTing a
> new record -- if the user has it already, then simply update values if
> needed and return the current row. Otherwise, INSERT new row. I do
> this check+update+insert with three SQLs. Is there one way of doing it
> in SQL in PG?

Stored procedure would do it. Alternativly you can reduce the number of
queries by one, by simply doing the UPDATE and if nothing is updated,
then doing the insert.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.