From: Bruno Wolff III <bruno@wolff.to>
To: Volkan YAZICI <volkan.yazici@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Combining two SELECTs by same filters
Date: Wed, 26 Oct 2005 13:29:10 -0500
Message-ID: <20051026182910.GH11447@wolff.to> (raw)
In-Reply-To: <7104a7370510260816l9262d19j808d23c023e8445e@mail.gmail.com>
References: <7104a7370510260816l9262d19j808d23c023e8445e@mail.gmail.com>
On Wed, Oct 26, 2005 at 18:16:13 +0300,
Volkan YAZICI <volkan.yazici@gmail.com> wrote:
> And I want to collect the count of sales at hour = 21 and hour = 22.
> For this purpose, I'm using below SELECT query:
>
> => SELECT
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 21:00:00'),
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 22:00:00');
> ?column? | ?column?
> ----------+----------
> 6 | 2
> (1 row)
>
> Isn't it possible to combine these two SELECTs as one. Because one of
> their filters are same: id = 2. I'm just trying to avoid making 2
> scans with nearly same filters.
Use an OR clause when checking the time. You will need to enclose it
in parenthesis because AND binds tighter than OR.
For consecutive hours you could use a range test. (In fact you could use
a range test even for one hour and it might be fasterdepending on your
data and what indexes you have.)
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-sql@postgresql.org
Cc: bruno@wolff.to, volkan.yazici@gmail.com
Subject: Re: Combining two SELECTs by same filters
In-Reply-To: <20051026182910.GH11447@wolff.to>
* 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