From: Russell Smith <mr-russ@pws.com.au>
To: pgsql-performance@postgresql.org
Subject: Re: Hardware upgrade for a high-traffic database
Date: Thu, 12 Aug 2004 09:52:20 +1000
Message-ID: <200408120952.20352.mr-russ@pws.com.au> (raw)
In-Reply-To: <200408111908.i7BJ8m0E094620@mail.gotfrag.com>
References: <200408111908.i7BJ8m0E094620@mail.gotfrag.com>
[snip]
>
> One question I do have though - you specifically mentioned NOW() as
> something to watch out for, in that it's mutable. We typically use COUNT()
> as a subselect to retrieve the number of associated rows to the current
> query. Additionally, we use NOW a lot, primarily to detect the status of a
> date, i.e.:
>
> SELECT id FROM subscriptions WHERE userid = 11111 AND timeend > NOW();
>
> Is there a better way to do this? I was under the impression that NOW() was
> pretty harmless, just to return a current timestamp.
>
NOW() will trigger unnessecary sequence scans. As it is unknown with prepared
query and function when the statement is run, the planner plans the query with
now as a variable. This can push the planner to a seq scan over and index scan.
I have seen this time and time again.
You can create your own immutable now, but don't use it in functions or prepared queries
or you will get wrong results.
> Based on feedback, I'm looking at a minor upgrade of our RAID controller to
> a 3ware 9000 series (SATA with cache, battery backup optional), and
> re-configuring it for RAID 10. It's a damn cheap upgrade at around $350 and
> an hour of downtime, so I figure that it's worth it for us to give it a
> shot.
>
> Thanks,
>
> Jason
Russell Smith
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mr-russ@pws.com.au
Subject: Re: Hardware upgrade for a high-traffic database
In-Reply-To: <200408120952.20352.mr-russ@pws.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox