postgres CASE and where clause

Question

Suppose i have a table like:

id  item  type  status  date
1   A     1     P       2018-04-01
2   B     2     A       2018-01-01
3   C     1     A       2018-01-02 
4   D     2     A       2018-04-11

Now query 1:

Select count(case when type=1 and status='A' and date<'2018-04-01' then id) as type1, 
count(case when type=2 and status='A' and date<'2018-04-01' then id)as type2
FROM table

AND query 2:

Select count(case when type=1 then id) as type1, 
    count(case when type=2 then id)as type2 
FROM table where status='A' and date < '2018-04-01'

are these both same, if yes whcih is better

Please add your current table schema.

McNets
– McNets

2018-04-12 11:00:30 +00:00
Commented Apr 12, 2018 at 11:00 — McNets
– McNets, Commented Apr 12, 2018 at 11:00

Tim Biegeleisen · Accepted Answer · 2018-04-12 10:59:00Z

1

If you have an index on the date column, then the second version would probably perform better than the first version. The reason for this is that Postgres would be able to filter off many records in the WHERE before even having to do the conditional aggregations in the SELECT clause.

If date doesn't have an index, then Postgres would have to touch every record in the table in either case.

answered Apr 12, 2018 at 10:59

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

McNets · Accepted Answer · 2018-04-12 11:14:05Z

With an schema like this:

create table tbl
(
    id     int primary key,
    item   text,
    type   int,
    status text, 
    date   date
);

insert into tbl values
(1, 'A', 1, 'P', '2018-04-01'),
(2, 'B', 2, 'A', '2018-01-01'),
(3, 'C', 1, 'A', '2018-01-02'),
(4, 'D', 2, 'A', '2018-04-11');

Second query performs a bit better due:

Rows Removed by Filter: 2

But neither of them use any index, for a large amount of data you should consider to add a new index and check performance again.

explain (analyze,buffers)
select count(case when type = 1 and status = 'A' and date < '2018-04-01' then id end) as type1, 
       count(case when type = 2 and status = 'A' and date < '2018-04-01' then id end) as type2
from   tbl;

| QUERY PLAN                                                                                            |
| :---------------------------------------------------------------------------------------------------- |
| Aggregate  (cost=33.40..33.41 rows=1 width=16) (actual time=0.022..0.022 rows=1 loops=1)              |
|   Buffers: shared hit=1                                                                               |
|   ->  Seq Scan on tbl  (cost=0.00..17.80 rows=780 width=44) (actual time=0.006..0.007 rows=4 loops=1) |
|         Buffers: shared hit=1                                                                         |
| Planning time: 0.171 ms                                                                               |
| Execution time: 0.121 ms                                                                              |

explain (analyze,buffers)
select count(case when type=1 then id end) as type1, 
       count(case when type=2 then id end)as type2 
from   tbl 
where  status = 'A' 
and    date < '2018-04-01';

| QUERY PLAN                                                                                         |
| :------------------------------------------------------------------------------------------------- |
| Aggregate  (cost=21.71..21.72 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=1)           |
|   Buffers: shared hit=1                                                                            |
|   ->  Seq Scan on tbl  (cost=0.00..21.70 rows=1 width=8) (actual time=0.004..0.005 rows=2 loops=1) |
|         Filter: ((date < '2018-04-01'::date) AND (status = 'A'::text))                             |
|         Rows Removed by Filter: 2                                                                  |
|         Buffers: shared hit=1                                                                      |
| Planning time: 0.084 ms                                                                            |
| Execution time: 0.032 ms                                                                           |

dbfiddle here

Collectives™ on Stack Overflow

postgres CASE and where clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related