Let me tell you a story.
Once upon a time this morning, I was trying to figure out why the fuck one of our servers had gone postal and exploded, leaving behind a stinking cloud of piss mist and gaseous fecal matter yet again.
Trawling through the logs of one of the web apps on the machine, I found a bunch of requests that had each taken 15 seconds to complete. After some investigation, I found that these requests were all doing a "
select count(*) ..." from a table with millions of rows. OMG. So I fired up a postgres client and did an
explainfor the same query, and it told me it was doing a sequential fucking scan to find the count!
I googled intensely, and what I found made my jaw drop and my tongue roll out like a red carpet: There is no way to optimize count operations in postgresql. Read here for a brief explanation.
OMG thanks postgres! Mind if I rape you?!
SolutionsThere are a bunch of work arounds you can find on google, but they all boil down to 2 approaches: cache the count, or guess the count.
Oh yeah. Nothing like a good trigger to make you feel like you just got probed by a bunch of aliens with a fetish for egg beaters.
How about rules?
Rules don't look all that bad— certainly much nicer than triggers.
You could also obtain an estimate of the count with this:
SELECT reltuples FROM pg_class WHERE oid = 'foos'::regclass::oid;
This will give you the count at the time of the last
My listsOk, so lets take a look at what impact this experience has had on my lists.
Postgres just jumped from my Christmas List over to my Shit List — fuck you postgres, I'm gonna find you, and I'm gonna take my knife, and I'm gonna use your cock for a sheath.
The List Of Things I Should Have Known By Now grew by 1 for
select countsucking the scrote.
The List Of Things I Should Have Played With By Now also grew by 1 for rules since this is the first time I've actively investigated them.