CODEWITHSUNDEEP
postgresql
Anarion
Anarion

Reputation: 2534

Postgresql - selecting from one partition is much slower then selecting from another

I have a simple table

CREATE TABLE public."ToProcessVideoIds" (
"Id" text NOT NULL,
"IsProcessed" bool NOT NULL,
CONSTRAINT "PK_ToProcessVideoIdsPartitioned" PRIMARY KEY ("Id", "IsProcessed")
)
PARTITION BY LIST ("IsProcessed");

with two partitions by a boolean field IsProcessed.

CREATE TABLE public."ToProcessVideoIds_processed" PARTITION OF public.ToProcessVideoIds 
FOR VALUES IN (true);


CREATE TABLE public."ToProcessVideoIds_unprocessed" PARTITION OF 
public.ToProcessVideoIds FOR VALUES IN (false);

when I filter by IsProcessed (or when I select directly from partition) it's much faster for processed partition 0.06ms vs 27ms

explain (ANALYZE, TIMING, COSTS, BUFFERS, VERBOSE)    select "Id" from "ToProcessVideoIds_unprocessed" limit 50;
Limit  (cost=0.00..0.81 rows=50 width=12) (actual time=27.350..27.358 rows=50 loops=1)
  Output: "Id"
  Buffers: shared hit=7265
  ->  Seq Scan on public."ToProcessVideoIds_unprocessed"  (cost=0.00..317346.99 rows=19697899 width=12) (actual time=27.349..27.353 rows=50 loops=1)
        Output: "Id"
        Buffers: shared hit=7265
Planning Time: 0.044 ms
Execution Time: 27.373 ms

_

explain (ANALYZE, TIMING, COSTS, BUFFERS, VERBOSE)    select "Id" from "ToProcessVideoIds_processed" limit 50;
Limit  (cost=0.00..0.77 rows=50 width=12) (actual time=0.040..0.049 rows=50 loops=1)
  Output: "Id"
  Buffers: shared hit=1
  ->  Seq Scan on public."ToProcessVideoIds_processed"  (cost=0.00..294045.98 rows=19038398 width=12) (actual time=0.040..0.044 rows=50 loops=1)
        Output: "Id"
        Buffers: shared hit=1
Planning Time: 0.030 ms
Execution Time: 0.059 ms

Did I configure something incorrectly? Is there a way to select from _unrpocessed partition with the same performance?

Records are updated all the time (unprocessed become processed) - could this affect anything?

There is roughly the same amount of data in both partitions.

Upvotes: 1

Views: 88

Answers (1)

Anarion
Anarion

Reputation: 2534

Records are updated all the time (unprocessed become processed) - could this affect anything?

this was good clue. vacuum helped. any performance advises here? Should I just define a more aggressive vacuum setting?

Upvotes: 1

Related Questions