1

Query:

with firstData as (
   select r.display_name as counter,
          r.createdat,
          r.status,
          r.id as runid
   from run r
      inner join customer c on c.id = r.badmasterid
      inner join bank b on b.id = c.registerid
   where b.displayname in ('abc', 'pqr')
   order by r.createdat desc limit 100
),
secondData as (
   select fi.runid,
          count(1) filter (where action = 'Add' and type = 'Bank') as addcount,
          count(1) filter (where action = 'Modify' and type = 'Bank') as modifycount,
          count(1) filter (where action = 'Delete' and type = 'Bank') as deletecount,
          count(1) filter (where action is null and type = 'Bank') as pendingcount,
          count(1) filter (where (status = 'FAILED' or status = 'FAILED') and type = 'Bank') as failcount
   from bigTable fi
   where fi.runid in
      (select runid from firstData limit 100)
   group by fi.runid
)
select r1.*,
       case when c1.add_count is null then 0 else c1.add_count end,
       case when c1.modify_count is null then 0 else c1.modify_count end,
       case when c1.delete_count is null then 0 else c1.delete_count end,
       case when c1.pending_count is null then 0 else c1.pending_count end,
       case when c1.fail_count is null then 0 else c1.fail_count end
from firstData r1
   left join secondData c1 on r1.runid = c1.runid
order by r1.createdat;

**Indexs on Table:**
run : id column
customer : id column
bank : id column
bigTable : (id, status, type, runid) columns

Execution Plan:

Sort  (cost=839936.19..839939.32 rows=1250 width=116) (actual time=7838.265..7838.275 rows=100 loops=1)
  Sort Key: r1.createdat
  Sort Method: quicksort  Memory: 34kB
  CTE result
    ->  Limit  (cost=79.27..79.52 rows=100 width=52) (actual time=1.601..1.615 rows=100 loops=1)
          ->  Sort  (cost=79.27..79.90 rows=250 width=52) (actual time=1.600..1.607 rows=100 loops=1)
                Sort Key: br.createdat DESC
                Sort Method: top-N heapsort  Memory: 35kB
                ->  Hash Join  (cost=2.84..69.72 rows=250 width=52) (actual time=0.101..1.083 rows=2500 loops=1)
                      Hash Cond: (r.runid = b.id)
                      ->  Seq Scan on run r  (cost=0.00..55.00 rows=2500 width=28) (actual time=0.029..0.213 rows=2500 loops=1)
                      ->  Hash  (cost=2.79..2.79 rows=4 width=36) (actual time=0.066..0.068 rows=40 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 10kB
                            ->  Hash Join  (cost=1.27..2.79 rows=4 width=36) (actual time=0.046..0.061 rows=40 loops=1)
                                  Hash Cond: (b.counterid = rm.id)
                                  ->  Seq Scan on bank b  (cost=0.00..1.40 rows=40 width=12) (actual time=0.011..0.014 rows=40 loops=1)
                                  ->  Hash  (cost=1.25..1.25 rows=2 width=36) (actual time=0.026..0.027 rows=20 loops=1)
                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                        ->  Seq Scan on customer c  (cost=0.00..1.25 rows=2 width=36) (actual time=0.012..0.018 rows=20 loops=1)
                                              Filter: (display_name = ANY ('{abc,pqr}'::text[]))
  ->  Hash Right Join  (cost=839720.50..839792.38 rows=1250 width=116) (actual time=7838.151..7838.230 rows=100 loops=1)
        Hash Cond: (fi.runid = r1.runid)
        ->  HashAggregate  (cost=839717.25..839742.25 rows=2500 width=48) (actual time=7836.481..7836.521 rows=100 loops=1)
              Group Key: fi.runid
              Batches: 1  Memory Usage: 145kB
              ->  Hash Semi Join  (cost=3.25..810917.25 rows=720000 width=31) (actual time=615.639..7395.089 rows=719888 loops=1)
                    Hash Cond: (fi.runid = firstData.runid)
                    ->  Seq Scan on bigTable fi  (cost=0.00..755654.00 rows=18000000 width=31) (actual time=20.792..5093.711 rows=18000000 loops=1)
                    ->  Hash  (cost=2.00..2.00 rows=100 width=4) (actual time=0.046..0.047 rows=100 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 12kB
                          ->  Limit  (cost=0.00..2.00 rows=100 width=4) (actual time=0.004..0.023 rows=100 loops=1)
                                ->  CTE Scan on firstData   (cost=0.00..2.00 rows=100 width=4) (actual time=0.003..0.017 rows=100 loops=1)
        ->  Hash  (cost=2.00..2.00 rows=100 width=76) (actual time=1.660..1.661 rows=100 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 15kB
              ->  CTE Scan on firstData r1  (cost=0.00..2.00 rows=100 width=76) (actual time=1.603..1.630 rows=100 loops=1)

Planning Time: 4.387 ms
Execution Time: 7838.458 ms

6
  • The format of the execution plan is broken. Can you fix it? The spaces in the beginning of the line are important. Feb 27 at 11:09
  • Hi @LaurenzAlbe updated. Feb 27 at 11:15
  • Thanks. Please remove those <br>. Feb 27 at 11:35
  • the plan on depesz. CTE seconddata is the problem. Since all aggregates have "type = 'Bank'" in the FILTER, what happens if you move "type = 'Bank'" to the WHERE in that CTE? When you say indices on (id, status, type, runid) is that 4 indices or a multicolumn index?
    – bobflux
    Feb 27 at 15:13
  • Hi @bobflux, it is 4 indices. Feb 28 at 5:46

1 Answer 1

1

Creating a test setup:

CREATE UNLOGGED TABLE bigtable (
    runid       INTEGER NOT NULL,
    type        TEXT NOT NULL,
    action      TEXT NULL,
    status      TEXT NULL
);

INSERT INTO bigtable
SELECT n, t.column1, a.column1, CASE WHEN random()<0.01 THEN 'FAILED' ELSE NULL END FROM 
    (VALUES ('Bank'),('Foo'),('Bar')) t,
    (VALUES ('Add'),('Modify'),('Delete'),(NULL)) a,
    generate_series(1,200) x,
    generate_series(1,25000) n;
VACUUM ANALYZE bigtable;

Let's try the problematic query:

EXPLAIN ANALYZE with firstData as MATERIALIZED (SELECT generate_series(1,100) runid)
    select fi.runid,
             count(1) filter (where action = 'Add' and type = 'Bank') as addcount,
             count(1) filter (where action = 'Modify' and type = 'Bank') as modifycount,
             count(1) filter (where action = 'Delete' and type = 'Bank') as deletecount,
             count(1) filter (where action is null and type = 'Bank') as pendingcount,
             count(1) filter (where (status = 'FAILED' or status = 'FAILED') and type = 'Bank') as failcount
    from bigTable fi JOIN firstData USING (runid)
    group by fi.runid;

Without any index, I get a plan similar to the one in the question, with the seq scan on bigtable. It's pretty slow.

CREATE INDEX ON bigtable( runid, type, action );
CREATE INDEX ON bigtable( runid, type, status );

Same query:

 HashAggregate  (cost=426190.72..426359.97 rows=16925 width=44) (actual time=111.246..111.331 rows=100 loops=1)
    Group Key: fi.runid
    Batches: 1  Memory Usage: 817kB
    CTE firstdata
      ->  ProjectSet  (cost=0.00..0.52 rows=100 width=4) (actual time=21.951..21.962 rows=100 loops=1)
              ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=21.946..21.946 rows=1 loops=1)
    ->  Nested Loop  (cost=0.56..412010.00 rows=354505 width=20) (actual time=21.978..66.854 rows=240000 loops=1)
            ->  CTE Scan on firstdata  (cost=0.00..2.00 rows=100 width=4) (actual time=21.954..21.993 rows=100 loops=1)
            ->  Index Scan using bigtable_runid_type_action_idx on bigtable fi  (cost=0.56..4084.63 rows=3545 width=20) (actual time=0.004..0.273 rows=2400 loops=100)
                    Index Cond: (runid = firstdata.runid)
 Planning Time: 0.408 ms
 Execution Time: 119.600 ms

This is about 100x faster. It is using the index I just created, but only to find "runid=...". It is not using the other columns, because postgres doesn't know how to do that with multiple FILTERs in aggregates. So a simple index on (runid) would work just as well.

If you have a multicolumn index on (id, status, type, runid) then it's useless for this because runid is the last column. besides, with id being the first column and unique, this multicolumn index can't do much more than an index on just (id), which you already have if it's the primary key, so unless you're using it for something very specific I think it can be removed.

Now the reason I created these two indices is to actually use them:

EXPLAIN ANALYZE with firstData as MATERIALIZED (SELECT generate_series(1,100) runid)
    select f.runid,
            (SELECT count(*) FROM bigtable b WHERE b.runid=f.runid AND action = 'Add'     and type = 'Bank') as addcount,
            (SELECT count(*) FROM bigtable b WHERE b.runid=f.runid AND action = 'Modify'  and type = 'Bank') as modifycount,
            (SELECT count(*) FROM bigtable b WHERE b.runid=f.runid AND action = 'Delete'  and type = 'Bank') as deletecount,
            (SELECT count(*) FROM bigtable b WHERE b.runid=f.runid AND action is null     and type = 'Bank') as pendingcount,
            (SELECT count(*) FROM bigtable b WHERE b.runid=f.runid AND status = 'FAILED'  and type = 'Bank') as failcount
    FROM firstData f;

 CTE Scan on firstdata f  (cost=0.52..5282.52 rows=100 width=44) (actual time=0.529..11.163 rows=100 loops=1)
    CTE firstdata
      ->  ProjectSet  (cost=0.00..0.52 rows=100 width=4) (actual time=0.006..0.017 rows=100 loops=1)
              ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
    SubPlan 2
      ->  Aggregate  (cost=12.12..12.12 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=100)
              ->  Index Only Scan using bigtable_runid_type_action_idx on bigtable b  (cost=0.56..11.36 rows=302 width=0) (actual time=0.004..0.019 rows=200 loops=100)
                      Index Cond: ((runid = f.runid) AND (type = 'Bank'::text) AND (action = 'Add'::text))
                      Heap Fetches: 0
    SubPlan 3
      ->  Aggregate  (cost=11.92..11.93 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=100)
              ->  Index Only Scan using bigtable_runid_type_action_idx on bigtable b_1  (cost=0.56..11.18 rows=294 width=0) (actual time=0.003..0.018 rows=200 loops=100)
                      Index Cond: ((runid = f.runid) AND (type = 'Bank'::text) AND (action = 'Modify'::text))
                      Heap Fetches: 0
    SubPlan 4
      ->  Aggregate  (cost=12.01..12.02 rows=1 width=8) (actual time=0.025..0.025 rows=1 loops=100)
              ->  Index Only Scan using bigtable_runid_type_action_idx on bigtable b_2  (cost=0.56..11.27 rows=298 width=0) (actual time=0.003..0.017 rows=200 loops=100)
                      Index Cond: ((runid = f.runid) AND (type = 'Bank'::text) AND (action = 'Delete'::text))
                      Heap Fetches: 0
    SubPlan 5
      ->  Aggregate  (cost=11.87..11.88 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=100)
              ->  Index Only Scan using bigtable_runid_type_action_idx on bigtable b_3  (cost=0.56..11.13 rows=292 width=0) (actual time=0.003..0.017 rows=200 loops=100)
                      Index Cond: ((runid = f.runid) AND (type = 'Bank'::text) AND (action IS NULL))
                      Heap Fetches: 0
    SubPlan 6
      ->  Aggregate  (cost=4.84..4.85 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=100)
              ->  Index Only Scan using bigtable_runid_type_status_idx on bigtable b_4  (cost=0.56..4.81 rows=11 width=0) (actual time=0.003..0.003 rows=7 loops=100)
                      Index Cond: ((runid = f.runid) AND (type = 'Bank'::text) AND (status = 'FAILED'::text))
                      Heap Fetches: 0
 Planning Time: 0.680 ms
 Execution Time: 11.313 ms
(31 rows)

This time, it's using index-only scans for everything, and we also got rid of the HashAggregates, so it's 10x faster than the previous one, for a total speedup of about 1000x.

But it needs two multicolumn indices, which take up resources. You could use these indices for other queries though. The previous one just needs a smaller index on runid (or one of the multicolumn indices).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.