Results 1 to 1 of 1
  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Unanswered: Postgresql filtering a grouped view

    I have a grouped view that I need to filter. My query is using the indexes correctly but the query plan gets some extra steps when I filter against a grouped view vs filtering the table directly and then grouping. Below is a stripped down example to show what's happening. Any way I can get the last query below to perform the same as the first? Postgresql 9.6
    Code:
    drop table if exists tbl cascade;
    create temp table tbl(val int primary key, val2 int not null, unique(val2, val));
    insert into tbl select val, val/10 from generate_series(1, 2000000) val;
    create temp view vw as select val2, count(*) cnt from tbl group by val2;
    --fast
    SELECT val2, COUNT(*) cnt FROM tbl WHERE val2 IN (1,2) GROUP BY val;
    SELECT val2, COUNT(*) cnt FROM tbl WHERE val2 IN (select 1 union select 2) GROUP BY val;
    SELECT val2, cnt FROM vw WHERE val2 IN (1,2);
    --slow
    SELECT val2, cnt FROM vw WHERE val2 IN (select 1 union select 2);
    Last edited by meyerovb; 12-01-16 at 11:37.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •