improve performance of large scan calculation in PostgreSQL table

Question

I have a table flags that collects user contributions for each distinct polygon (flag_value, in this case a 'YES') during their usersession. The idea is to count how many users said YES out of the total contributions (could be NO or FIX or some other indeterminate string). The flags table has over two million rows. Added EXPLAIN ANALYZE for additional information:

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT POSITIVE.flag_score AS p_score, TOTAL.flag_score AS t_score, 'yes' AS consensus, 'geometry' AS task, P.id AS polygon_id, 'Polygon', now(), now()
  FROM polygons AS P
  LEFT JOIN consensuspolygons AS C
  ON C.flaggable_id = P.id
  AND C.task = 'geometry'
  INNER JOIN (
    SELECT _F.flaggable_id, COUNT(*) AS flag_count, SUM(CASE WHEN _U.role = 'admin' THEN 4 ELSE 1 END) AS flag_score
    FROM flags AS _F

    LEFT JOIN usersessions _S
    ON _S.session_id = _F.session_id
    LEFT JOIN users _U
    ON _U.id = _S.user_id

    WHERE _F.flag_value = 'yes'
    AND _F.flag_type = 'geometry'
    AND _F.flaggable_type = 'Polygon'
    GROUP BY _F.flaggable_id
    HAVING COUNT(*) >= 1
  ) AS POSITIVE
  ON POSITIVE.flaggable_id = P.id
  INNER JOIN (
    SELECT _F.flaggable_id, COUNT(*) AS flag_count, SUM(CASE WHEN _U.role = 'admin' THEN 4 ELSE 1 END) AS flag_score
    FROM flags AS _F

    LEFT JOIN usersessions _S
    ON _S.session_id = _F.session_id
    LEFT JOIN users _U
    ON _U.id = _S.user_id

    WHERE _F.flag_type = 'geometry'
    AND _F.flaggable_type = 'Polygon'
    GROUP BY _F.flaggable_id
    HAVING COUNT(*) >= 1
  ) AS TOTAL
  ON TOTAL.flaggable_id = P.id

An example result would be something like:

p_score	t_score	consensus	task	polygon_id	?column?	now	now
11	11	"yes"	"geometry"	73000	"Polygon"	"2021-10-31 11:19:18.669106-04"	"2021-10-31 11:19:18.669106-04"
6	9	"yes"	"geometry"	73001	"Polygon"	"2021-10-31 11:19:18.669106-04"	"2021-10-31 11:19:18.669106-04"
1	9	"yes"	"geometry"	73002	"Polygon"	"2021-10-31 11:19:18.669106-04"	"2021-10-31 11:19:18.669106-04"

The query works but it takes a second or two for each polygon. Per the EXPLAIN below, I see the sequential scans are the costliest/largest but I'm unsure as to how to what indices are required. I have tried:

indices on session_id and user_id in usersessions
combined index on (flag_value, flag_type, flaggable_type) in flags
combined index on (flag_value, flaggable_type) in flags

Any ideas would be welcome. Below the query plan and table SQLs:

Limit  (cost=146866.65..146984.55 rows=5 width=132) (actual time=1376.759..1419.053 rows=5 loops=1)
  Output: (sum(CASE WHEN ((_u.role)::text = 'admin'::text) THEN 4 ELSE 1 END)), (sum(CASE WHEN ((_u_1.role)::text = 'admin'::text) THEN 4 ELSE 1 END)), 'yes'::text, 'geometry'::text, p.id, 'Polygon'::text, (now()), (now())
  Buffers: shared hit=31936 read=44380, temp read=3953 written=6248
  ->  Merge Join  (cost=146866.65..179857.02 rows=1399 width=132) (actual time=1376.758..1419.051 rows=5 loops=1)
        Output: (sum(CASE WHEN ((_u.role)::text = 'admin'::text) THEN 4 ELSE 1 END)), (sum(CASE WHEN ((_u_1.role)::text = 'admin'::text) THEN 4 ELSE 1 END)), 'yes'::text, 'geometry'::text, p.id, 'Polygon'::text, now(), now()
        Inner Unique: true
        Merge Cond: (p.id = _f_1.flaggable_id)
        Buffers: shared hit=31936 read=44380, temp read=3953 written=6248
        ->  Merge Join  (cost=61610.62..81382.32 rows=16170 width=16) (actual time=475.174..501.509 rows=5 loops=1)
              Output: p.id, (sum(CASE WHEN ((_u.role)::text = 'admin'::text) THEN 4 ELSE 1 END)), _f.flaggable_id
              Inner Unique: true
              Merge Cond: (p.id = _f.flaggable_id)
              Buffers: shared hit=15960 read=22238, temp read=1148 written=1248
              ->  Index Only Scan using polygons_pkey on public.polygons p  (cost=0.42..4977.43 rows=191267 width=4) (actual time=0.007..0.011 rows=6 loops=1)
                    Output: p.id
                    Heap Fetches: 0
                    Buffers: shared hit=4
              ->  Finalize GroupAggregate  (cost=61610.20..75562.91 rows=16170 width=20) (actual time=475.164..501.492 rows=5 loops=1)
                    Output: _f.flaggable_id, NULL::bigint, sum(CASE WHEN ((_u.role)::text = 'admin'::text) THEN 4 ELSE 1 END)
                    Group Key: _f.flaggable_id
                    Filter: (count(*) >= 1)
                    Buffers: shared hit=15956 read=22238, temp read=1148 written=1248
                    ->  Gather Merge  (cost=61610.20..74228.88 rows=97020 width=20) (actual time=475.144..501.478 rows=13 loops=1)
                          Output: _f.flaggable_id, (PARTIAL sum(CASE WHEN ((_u.role)::text = 'admin'::text) THEN 4 ELSE 1 END)), (PARTIAL count(*))
                          Workers Planned: 2
                          Workers Launched: 2
                          Buffers: shared hit=15956 read=22238, temp read=1148 written=1248
                          ->  Partial GroupAggregate  (cost=60610.18..62030.34 rows=48510 width=20) (actual time=464.223..465.270 rows=781 loops=3)
                                Output: _f.flaggable_id, PARTIAL sum(CASE WHEN ((_u.role)::text = 'admin'::text) THEN 4 ELSE 1 END), PARTIAL count(*)
                                Group Key: _f.flaggable_id
                                Buffers: shared hit=15956 read=22238, temp read=1148 written=1248
                                Worker 0:  actual time=463.560..465.163 rows=1170 loops=1
                                  Buffers: shared hit=4716 read=7226, temp read=379 written=413
                                Worker 1:  actual time=469.522..471.025 rows=1170 loops=1
                                  Buffers: shared hit=4957 read=7185, temp read=379 written=414
                                ->  Sort  (cost=60610.18..60797.19 rows=74805 width=13) (actual time=464.204..464.587 rows=2101 loops=3)
                                      Output: _f.flaggable_id, _u.role
                                      Sort Key: _f.flaggable_id
                                      Sort Method: external merge  Disk: 3344kB
                                      Buffers: shared hit=15956 read=22238, temp read=1148 written=1248
                                      Worker 0:  actual time=463.542..464.124 rows=3115 loops=1
                                        Sort Method: external merge  Disk: 3288kB
                                        Buffers: shared hit=4716 read=7226, temp read=379 written=413
                                      Worker 1:  actual time=469.497..470.054 rows=3174 loops=1
                                        Sort Method: external merge  Disk: 3296kB
                                        Buffers: shared hit=4957 read=7185, temp read=379 written=414
                                      ->  Hash Left Join  (cost=241.31..54554.40 rows=74805 width=13) (actual time=4.276..359.295 rows=170970 loops=3)
                                            Output: _f.flaggable_id, _u.role
                                            Hash Cond: ((_f.session_id)::text = (_s.session_id)::text)
                                            Buffers: shared hit=15882 read=22238
                                            Worker 0:  actual time=4.279..349.064 rows=169302 loops=1
                                              Buffers: shared hit=4679 read=7226
                                            Worker 1:  actual time=4.314..354.762 rows=169343 loops=1
                                              Buffers: shared hit=4920 read=7185
                                            ->  Parallel Seq Scan on public.flags _f  (cost=0.00..53444.43 rows=74805 width=37) (actual time=0.158..292.951 rows=170379 loops=3)
                                                  Output: _f.id, _f.flag_type, _f.flaggable_id, _f.session_id, _f.flag_value, _f.is_primary, _f.created_at, _f.updated_at, _f.latitude, _f.longitude, _f.flaggable_type
                                                  Filter: ((_f.flag_value = 'yes'::text) AND ((_f.flag_type)::text = 'geometry'::text) AND ((_f.flaggable_type)::text = 'Polygon'::text))
                                                  Rows Removed by Filter: 545343
                                                  Buffers: shared hit=15550 read=22238
                                                  Worker 0:  actual time=0.030..277.750 rows=168670 loops=1
                                                    Buffers: shared hit=4560 read=7226
                                                  Worker 1:  actual time=0.026..282.599 rows=168781 loops=1
                                                    Buffers: shared hit=4801 read=7185
                                            ->  Hash  (cost=187.94..187.94 rows=4270 width=42) (actual time=4.064..4.069 rows=4270 loops=3)
                                                  Output: _s.session_id, _u.role
                                                  Buckets: 8192  Batches: 1  Memory Usage: 377kB
                                                  Buffers: shared hit=304
                                                  Worker 0:  actual time=4.161..4.167 rows=4270 loops=1
                                                    Buffers: shared hit=105
                                                  Worker 1:  actual time=4.218..4.225 rows=4270 loops=1
                                                    Buffers: shared hit=105
                                                  ->  Hash Left Join  (cost=85.00..187.94 rows=4270 width=42) (actual time=0.871..2.891 rows=4270 loops=3)
                                                        Output: _s.session_id, _u.role
                                                        Inner Unique: true
                                                        Hash Cond: (_s.user_id = _u.id)
                                                        Buffers: shared hit=304
                                                        Worker 0:  actual time=1.027..2.941 rows=4270 loops=1
                                                          Buffers: shared hit=105
                                                        Worker 1:  actual time=0.922..3.006 rows=4270 loops=1
                                                          Buffers: shared hit=105
                                                        ->  Seq Scan on public.usersessions _s  (cost=0.00..91.70 rows=4270 width=37) (actual time=0.031..0.590 rows=4270 loops=3)
                                                              Output: _s.id, _s.user_id, _s.session_id, _s.created_at, _s.updated_at
                                                              Buffers: shared hit=147
                                                              Worker 0:  actual time=0.050..0.604 rows=4270 loops=1
                                                                Buffers: shared hit=49
                                                              Worker 1:  actual time=0.041..0.639 rows=4270 loops=1
                                                                Buffers: shared hit=49
                                                        ->  Hash  (cost=62.78..62.78 rows=1778 width=13) (actual time=0.816..0.816 rows=1778 loops=3)
                                                              Output: _u.role, _u.id
                                                              Buckets: 2048  Batches: 1  Memory Usage: 100kB
                                                              Buffers: shared hit=135
                                                              Worker 0:  actual time=0.938..0.939 rows=1778 loops=1
                                                                Buffers: shared hit=45
                                                              Worker 1:  actual time=0.853..0.853 rows=1778 loops=1
                                                                Buffers: shared hit=45
                                                              ->  Seq Scan on public.users _u  (cost=0.00..62.78 rows=1778 width=13) (actual time=0.026..0.529 rows=1778 loops=3)
                                                                    Output: _u.role, _u.id
                                                                    Buffers: shared hit=135
                                                                    Worker 0:  actual time=0.040..0.615 rows=1778 loops=1
                                                                      Buffers: shared hit=45
                                                                    Worker 1:  actual time=0.033..0.574 rows=1778 loops=1
                                                                      Buffers: shared hit=45
        ->  Finalize GroupAggregate  (cost=85256.02..98206.42 rows=16549 width=20) (actual time=901.579..917.534 rows=6 loops=1)
              Output: _f_1.flaggable_id, NULL::bigint, sum(CASE WHEN ((_u_1.role)::text = 'admin'::text) THEN 4 ELSE 1 END)
              Group Key: _f_1.flaggable_id
              Filter: (count(*) >= 1)
              Buffers: shared hit=15976 read=22142, temp read=2805 written=5000
              ->  Gather Merge  (cost=85256.02..96841.13 rows=99294 width=20) (actual time=901.566..917.519 rows=19 loops=1)
                    Output: _f_1.flaggable_id, (PARTIAL sum(CASE WHEN ((_u_1.role)::text = 'admin'::text) THEN 4 ELSE 1 END)), (PARTIAL count(*))
                    Workers Planned: 2
                    Workers Launched: 2
                    Buffers: shared hit=15976 read=22142, temp read=2805 written=5000
                    ->  Sort  (cost=84256.00..84380.12 rows=49647 width=20) (actual time=884.539..884.650 rows=782 loops=3)
                          Output: _f_1.flaggable_id, (PARTIAL sum(CASE WHEN ((_u_1.role)::text = 'admin'::text) THEN 4 ELSE 1 END)), (PARTIAL count(*))
                          Sort Key: _f_1.flaggable_id
                          Sort Method: external merge  Disk: 3488kB
                          Buffers: shared hit=15976 read=22142, temp read=2805 written=5000
                          Worker 0:  actual time=877.889..878.075 rows=1170 loops=1
                            Sort Method: external merge  Disk: 3424kB
                            Buffers: shared hit=4826 read=7010, temp read=930 written=1661
                          Worker 1:  actual time=877.126..877.268 rows=1170 loops=1
                            Sort Method: external merge  Disk: 3376kB
                            Buffers: shared hit=4873 read=6965, temp read=917 written=1647
                          ->  Partial HashAggregate  (cost=76816.50..80383.68 rows=49647 width=20) (actual time=686.588..775.954 rows=102917 loops=3)
                                Output: _f_1.flaggable_id, PARTIAL sum(CASE WHEN ((_u_1.role)::text = 'admin'::text) THEN 4 ELSE 1 END), PARTIAL count(*)
                                Group Key: _f_1.flaggable_id
                                Batches: 5  Memory Usage: 4145kB  Disk Usage: 6992kB
                                Buffers: shared hit=15962 read=22142, temp read=1519 written=3708
                                Worker 0:  actual time=678.709..778.991 rows=102760 loops=1
                                  Batches: 5  Memory Usage: 4145kB  Disk Usage: 6944kB
                                  Buffers: shared hit=4819 read=7010, temp read=502 written=1231
                                Worker 1:  actual time=679.094..762.217 rows=101274 loops=1
                                  Batches: 5  Memory Usage: 4145kB  Disk Usage: 6928kB
                                  Buffers: shared hit=4866 read=6965, temp read=495 written=1223
                                ->  Hash Left Join  (cost=241.31..55100.49 rows=314440 width=13) (actual time=4.553..446.632 rows=297380 loops=3)
                                      Output: _f_1.flaggable_id, _u_1.role
                                      Hash Cond: ((_f_1.session_id)::text = (_s_1.session_id)::text)
                                      Buffers: shared hit=15962 read=22142
                                      Worker 0:  actual time=5.245..455.337 rows=288616 loops=1
                                        Buffers: shared hit=4819 read=7010
                                      Worker 1:  actual time=4.870..422.737 rows=290016 loops=1
                                        Buffers: shared hit=4866 read=6965
                                      ->  Parallel Seq Scan on public.flags _f_1  (cost=0.00..51207.80 rows=314440 width=37) (actual time=0.084..309.027 rows=296552 loops=3)
                                            Output: _f_1.id, _f_1.flag_type, _f_1.flaggable_id, _f_1.session_id, _f_1.flag_value, _f_1.is_primary, _f_1.created_at, _f_1.updated_at, _f_1.latitude, _f_1.longitude, _f_1.flaggable_type
                                            Filter: (((_f_1.flag_type)::text = 'geometry'::text) AND ((_f_1.flaggable_type)::text = 'Polygon'::text))
                                            Rows Removed by Filter: 419171
                                            Buffers: shared hit=15646 read=22142
                                            Worker 0:  actual time=0.140..325.695 rows=287887 loops=1
                                              Buffers: shared hit=4708 read=7010
                                            Worker 1:  actual time=0.023..276.184 rows=289219 loops=1
                                              Buffers: shared hit=4755 read=6965
                                      ->  Hash  (cost=187.94..187.94 rows=4270 width=42) (actual time=4.383..4.388 rows=4270 loops=3)
                                            Output: _s_1.session_id, _u_1.role
                                            Buckets: 8192  Batches: 1  Memory Usage: 377kB
                                            Buffers: shared hit=288
                                            Worker 0:  actual time=4.952..4.958 rows=4270 loops=1
                                              Buffers: shared hit=97
                                            Worker 1:  actual time=4.754..4.760 rows=4270 loops=1
                                              Buffers: shared hit=97
                                            ->  Hash Left Join  (cost=85.00..187.94 rows=4270 width=42) (actual time=1.082..3.155 rows=4270 loops=3)
                                                  Output: _s_1.session_id, _u_1.role
                                                  Inner Unique: true
                                                  Hash Cond: (_s_1.user_id = _u_1.id)
                                                  Buffers: shared hit=288
                                                  Worker 0:  actual time=1.307..3.560 rows=4270 loops=1
                                                    Buffers: shared hit=97
                                                  Worker 1:  actual time=1.264..3.393 rows=4270 loops=1
                                                    Buffers: shared hit=97
                                                  ->  Seq Scan on public.usersessions _s_1  (cost=0.00..91.70 rows=4270 width=37) (actual time=0.043..0.609 rows=4270 loops=3)
                                                        Output: _s_1.id, _s_1.user_id, _s_1.session_id, _s_1.created_at, _s_1.updated_at
                                                        Buffers: shared hit=147
                                                        Worker 0:  actual time=0.068..0.708 rows=4270 loops=1
                                                          Buffers: shared hit=49
                                                        Worker 1:  actual time=0.058..0.640 rows=4270 loops=1
                                                          Buffers: shared hit=49
                                                  ->  Hash  (cost=62.78..62.78 rows=1778 width=13) (actual time=1.020..1.020 rows=1778 loops=3)
                                                        Output: _u_1.role, _u_1.id
                                                        Buckets: 2048  Batches: 1  Memory Usage: 100kB
                                                        Buffers: shared hit=135
                                                        Worker 0:  actual time=1.211..1.211 rows=1778 loops=1
                                                          Buffers: shared hit=45
                                                        Worker 1:  actual time=1.182..1.182 rows=1778 loops=1
                                                          Buffers: shared hit=45
                                                        ->  Seq Scan on public.users _u_1  (cost=0.00..62.78 rows=1778 width=13) (actual time=0.028..0.644 rows=1778 loops=3)
                                                              Output: _u_1.role, _u_1.id
                                                              Buffers: shared hit=135
                                                              Worker 0:  actual time=0.050..0.798 rows=1778 loops=1
                                                                Buffers: shared hit=45
                                                              Worker 1:  actual time=0.030..0.778 rows=1778 loops=1
                                                                Buffers: shared hit=45
Planning:
  Buffers: shared hit=32
Planning Time: 0.594 ms
Execution Time: 1447.677 ms

Tables:

CREATE TABLE public.consensuspolygons
(
    id integer NOT NULL DEFAULT nextval('consensuspolygons_id_seq'::regclass),
    task character varying(255) COLLATE pg_catalog."default",
    flaggable_id integer,
    consensus text COLLATE pg_catalog."default",
    flaggable_type character varying(255) COLLATE pg_catalog."default" DEFAULT 'Polygon'::character varying,
    CONSTRAINT consensuspolygons_pkey PRIMARY KEY (id)
)

CREATE INDEX index_task
    ON public.consensuspolygons USING btree
    (task COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE UNIQUE INDEX index_task_consensus_on_polygon_id
    ON public.consensuspolygons USING btree
    (flaggable_id ASC NULLS LAST, task COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE TABLE public.flags
(
    id integer NOT NULL DEFAULT nextval('flags_id_seq'::regclass),
    flag_type character varying(255) COLLATE pg_catalog."default",
    flaggable_id integer,
    session_id character varying(255) COLLATE pg_catalog."default",
    flag_value text COLLATE pg_catalog."default",
    flaggable_type character varying(255) COLLATE pg_catalog."default" DEFAULT 'Polygon'::character varying,
    CONSTRAINT flags_pkey PRIMARY KEY (id)
)

CREATE UNIQUE INDEX index_flags_on_session_id
    ON public.flags USING btree
    (session_id COLLATE pg_catalog."default" ASC NULLS LAST, flag_type COLLATE pg_catalog."default" ASC NULLS LAST, flaggable_id ASC NULLS LAST, flag_value COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX polygon_index
    ON public.flags USING btree
    (flaggable_id ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE TABLE public.usersessions
(
    id integer NOT NULL DEFAULT nextval('usersessions_id_seq'::regclass),
    user_id integer,
    session_id character varying(255) COLLATE pg_catalog."default",
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    CONSTRAINT usersessions_pkey PRIMARY KEY (id)
)

Edit:

The new query suggested by @jjanes (DB Fiddle):

SELECT flag_score.flag_score_pos, flag_score.flag_score_tot, 'yes' AS consensus, 'geometry' AS task, P.id AS polygon_id, 'Polygon', now(), now()
  FROM polygons AS P
  LEFT JOIN consensuspolygons AS C
  ON C.flaggable_id = P.id
  AND C.task = 'geometry'
  INNER JOIN (
    SELECT _F.flaggable_id, 
    SUM(CASE WHEN _U.role = 'admin' THEN 4 ELSE 1 END) AS flag_score_tot,
    SUM(CASE WHEN _U.role = 'admin' THEN 4 ELSE 1 END) FILTER (WHERE flag_value = 'yes') AS flag_score_pos
    FROM flags AS _F    
    LEFT JOIN usersessions _S
    ON _S.session_id = _F.session_id
    LEFT JOIN users _U
    ON _U.id = _S.user_id    
    WHERE _F.flag_type = 'geometry'
    AND _F.flaggable_type = 'Polygon'
    GROUP BY _F.flaggable_id
    HAVING COUNT(*) FILTER (WHERE flag_value = 'yes') >= 1
  ) AS flag_score
ON flag_score.flaggable_id = P.id

It works but is “only” about a third faster (~1.08s instead of ~1.44s for almost 300k rows) since it is saving one of the two sequential scans. Is there an index hat could be added to optimize that sequential scan?

Query plan:

Merge Join  (cost=97612.95..116721.86 rows=16549 width=132) (actual time=740.885..1039.578 rows=106725 loops=1)
  Merge Cond: (p.id = _f.flaggable_id)
  ->  Index Only Scan using polygons_pkey on polygons p  (cost=0.42..4977.43 rows=191267 width=4) (actual time=0.011..24.771 rows=191265 loops=1)
        Heap Fetches: 0
  ->  Finalize GroupAggregate  (cost=97612.53..110811.17 rows=16549 width=20) (actual time=740.869..957.634 rows=107660 loops=1)
        Group Key: _f.flaggable_id
        Filter: (count(*) FILTER (WHERE (_f.flag_value = 'yes'::text)) >= 1)
        Rows Removed by Filter: 30324
        ->  Gather Merge  (cost=97612.53..109197.64 rows=99294 width=28) (actual time=740.856..855.408 rows=309388 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              ->  Sort  (cost=96612.51..96736.63 rows=49647 width=28) (actual time=735.751..761.491 rows=103129 loops=3)
                    Sort Key: _f.flaggable_id
                    Sort Method: external merge  Disk: 4136kB
                    Worker 0:  Sort Method: external merge  Disk: 4024kB
                    Worker 1:  Sort Method: external merge  Disk: 4008kB
                    ->  Partial HashAggregate  (cost=87330.59..92740.19 rows=49647 width=28) (actual time=546.162..643.903 rows=103129 loops=3)
                          Group Key: _f.flaggable_id
                          Planned Partitions: 4  Batches: 5  Memory Usage: 4145kB  Disk Usage: 7128kB
                          Worker 0:  Batches: 5  Memory Usage: 4145kB  Disk Usage: 7064kB
                          Worker 1:  Batches: 5  Memory Usage: 4145kB  Disk Usage: 7056kB
                          ->  Hash Left Join  (cost=241.31..55100.49 rows=314440 width=34) (actual time=3.961..325.059 rows=297380 loops=3)
                                Hash Cond: ((_f.session_id)::text = (_s.session_id)::text)
                                ->  Parallel Seq Scan on flags _f  (cost=0.00..51207.80 rows=314440 width=58) (actual time=0.098..233.054 rows=296552 loops=3)
                                      Filter: (((flag_type)::text = 'geometry'::text) AND ((flaggable_type)::text = 'Polygon'::text))
                                      Rows Removed by Filter: 419171
                                ->  Hash  (cost=187.94..187.94 rows=4270 width=42) (actual time=3.812..3.817 rows=4270 loops=3)
                                      Buckets: 8192  Batches: 1  Memory Usage: 377kB
                                      ->  Hash Left Join  (cost=85.00..187.94 rows=4270 width=42) (actual time=0.848..2.711 rows=4270 loops=3)
                                            Hash Cond: (_s.user_id = _u.id)
                                            ->  Seq Scan on usersessions _s  (cost=0.00..91.70 rows=4270 width=37) (actual time=0.024..0.556 rows=4270 loops=3)
                                            ->  Hash  (cost=62.78..62.78 rows=1778 width=13) (actual time=0.807..0.808 rows=1778 loops=3)
                                                  Buckets: 2048  Batches: 1  Memory Usage: 100kB
                                                  ->  Seq Scan on users _u  (cost=0.00..62.78 rows=1778 width=13) (actual time=0.020..0.539 rows=1778 loops=3)
Planning Time: 0.488 ms
Execution Time: 1086.747 ms

total and positive can be obtained with a single select using case. — Gerard H. Pille, Commented Oct 31, 2021 at 15:49
Right now you are doing two sequential reads of it. One seems better than two. — jjanes, Commented Oct 31, 2021 at 16:39
yes but what would the optimization of that sequential read be? — mga, Commented Oct 31, 2021 at 16:58

jjanes · Accepted Answer · 2021-10-31 19:12:32Z

0

You can use FILTER to add an extra condition to one aggregate but not the others, so your two subquery could be reduced to something like:

 INNER JOIN (
    SELECT _F.flaggable_id, 
    SUM(CASE WHEN _U.role = 'admin' THEN 4 ELSE 1 END) AS flag_score_tot,
    SUM(CASE WHEN _U.role = 'admin' THEN 4 ELSE 1 END) FILTER (WHERE flag_value = 'yes') AS flag_score_pos
    FROM flags AS _F    
    LEFT JOIN usersessions _S
    ON _S.session_id = _F.session_id
    LEFT JOIN users _U
    ON _U.id = _S.user_id    
    WHERE _F.flag_type = 'geometry'
    AND _F.flaggable_type = 'Polygon'
    GROUP BY _F.flaggable_id
    HAVING COUNT(*) FILTER (WHERE flag_value = 'yes') >= 1
  ) AS flag_score

Another thing that might help is increasing work_mem enough that the hashagg and sort don't spill to disk.

answered Oct 31, 2021 at 19:12

jjanes

41.3k3 gold badges40 silver badges54 bronze badges

so i replace each subquery with this? wouldn't i still have two queries and therefore two scans anyway? sadly changing the work_mem is not an option. an index/query-based solution is required
– mga
Commented Nov 1, 2021 at 22:48
You would replace both subqueries with this. It returns both columns you want, simultaneously.
– jjanes
Commented Nov 1, 2021 at 23:35
added an edit with your suggestion
– mga
Commented Nov 2, 2021 at 1:58
im going to accept this answer bacause it does improve the original query. however, i was expecting more of an index-based solution.
– mga
Commented Nov 3, 2021 at 17:56

Add a comment |

Gerard H. Pille · Accepted Answer · 2021-11-02 10:06:26Z

Reduce complexity of the query

SELECT
  SUM(case when _F.flag_value = 'yes' then 1 else 0 end) as pos_count,
  COUNT(*) as tot_count,
  SUM(case
        when _F.flag_value = 'yes' then
          CASE WHEN _U.role = 'admin' THEN 4 ELSE 1 END
        else 0 end) as pos_score,
  SUM(CASE WHEN _U.role = 'admin' THEN 4 ELSE 1 END) as tot_score,
  'yes' AS consensus,
  'geometry' AS task,
  P.id AS polygon_id,
  'Polygon', now(), now()
  FROM polygons AS P
  /*
  LEFT JOIN consensuspolygons AS C
         ON C.flaggable_id = P.id
         AND C.task = 'geometry'
  */
  INNER JOIN flags AS _F
          ON _F.flaggable_id = P.id
          AND _F.flag_type = 'geometry'
          AND _F.flaggable_type = 'Polygon'
  LEFT JOIN usersessions _S
         ON _S.session_id = _F.session_id
  LEFT JOIN users _U
         ON _U.id = _S.user_id
  group by P.id
    having SUM(case when _F.flag_value = 'yes' then 1 else 0 end) > 0
  order by P.id
;

db fiddle

Stack Exchange Network

improve performance of large scan calculation in PostgreSQL table

2 Answers 2

Your Answer

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

Hot Network Questions

improve performance of large scan calculation in PostgreSQL table

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions

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