0

I have this table:

CREATE TABLE public.player (
    company_id character varying NOT NULL,
    id character varying NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp with time zone,
    name character varying NOT NULL,
    family character varying,
    start_from timestamp with time zone DEFAULT now() NOT NULL,
    surrogate_key character varying NOT NULL
);

I'm trying to implement a SCD (Slowly Changing Dimension) Type 2.

So if I try this query:

SELECT DISTINCT ON ( surrogate_key ) surrogate_key, * FROM player ORDER BY surrogate_key, created_at DESC;

it works. But the explain analyze is:

Unique  (cost=10.47..10.99 rows=101 width=171) (actual time=0.098..0.115 rows=101 loops=1)
  ->  Sort  (cost=10.47..10.73 rows=103 width=171) (actual time=0.097..0.100 rows=103 loops=1)
        Sort Key: surrogate_key, created_at DESC
        Sort Method: quicksort  Memory: 46kB
        ->  Seq Scan on player  (cost=0.00..7.03 rows=103 width=171) (actual time=0.013..0.039 rows=103 loops=1)
Planning Time: 0.076 ms
Execution Time: 0.135 ms

So I created an index:

CREATE INDEX ON player (surrogate_key, created_at DESC);

but the explain analyze is the same:

Unique  (cost=10.47..10.99 rows=101 width=171) (actual time=0.105..0.122 rows=101 loops=1)
  ->  Sort  (cost=10.47..10.73 rows=103 width=171) (actual time=0.104..0.107 rows=103 loops=1)
        Sort Key: surrogate_key, created_at DESC
        Sort Method: quicksort  Memory: 46kB
        ->  Seq Scan on player  (cost=0.00..7.03 rows=103 width=171) (actual time=0.010..0.045 rows=103 loops=1)
Planning Time: 0.081 ms
Execution Time: 0.139 ms

Is it possible? Am I doing index wrong?

I expect that there is no longer the seq scan with the index. Am I wrong?

Fred Hors
  • 3,258
  • 3
  • 25
  • 71
  • For such a tiny table a Seq Scan will always be the fastest way. If you want see the effect of the index you will need much more rows. –  Mar 27 '23 at 08:49
  • I tried with 1 million. – Fred Hors Mar 27 '23 at 08:53
  • Not with the plans in your question. –  Mar 27 '23 at 08:55
  • What do you mean? Anyway I tried it now and it works, you're right. I was wrong in my test with 1 million. The explain analyze is now: – Fred Hors Mar 27 '23 at 09:00
  • `Unique (cost=0.42..18014.26 rows=99984 width=145) (actual time=0.019..63.597 rows=100001 loops=1) -> Index Scan using player_surrogate_key_created_at_idx on player (cost=0.42..17764.26 rows=100003 width=145) (actual time=0.018..35.547 rows=100003 loops=1) Planning Time: 0.091 ms Execution Time: 65.696 ms` – Fred Hors Mar 27 '23 at 09:01
  • It works with 100 000 too. But is there a way to speed up more? – Fred Hors Mar 27 '23 at 09:01

0 Answers0