Postgres: Analyze after creating index or before

Question

I've got Postgres 11 version and production server. A procedure is rather slow and query (a piece of code in procedure) is something like that:

    create temp table tmp_pos_source as
        with ... (
            ...
        )
        , cte_emp as (
            ...
        )
        , cte_all as (
            ...
        )
        select  ...
        from    cte_all;
        analyze tmp_pos_source;

The query is slow and I want to create an index to improve speed.

create index idx_pos_obj_id on tmp_pos_source(pos_obj_id);

Where should I put it? After command ANALYZE or before?

A forgot to mention that this temporary table is used in further code in several join on filed pos_obj_id. So thats why i want to create index — Gerzzog
– Gerzzog, Commented Dec 12, 2022 at 8:23
Could you please share the DDL for the tables involved and the results from EXPLAIN(ANALYZE, VERBOSE, BUFFERS, COSTS) for the slow query? (in plain text). When a query is slow, you need the query plan to see where the time is spent and how to improve this. An index might help, but it's not a magic bullet: You need the right index. — Frank Heikens
– Frank Heikens, Commented Dec 12, 2022 at 8:36
Ok, I'll try. It is on production server and I can't retrieve it immediately) I was told that this piece of procedure (with further joins) lasts much time. But in theory - if index is needed, where should i put ANALYZE - after or before index? — Gerzzog
– Gerzzog, Commented Dec 12, 2022 at 8:43
You have to analyze anyway and an index doesn't improve the speed of analyze. The statistics aren't used by the index either. — Frank Heikens
– Frank Heikens, Commented Dec 12, 2022 at 8:49
Ok. I mean if I start analyze before creating index then statistics of temp table will not use the statistic of index...maybe I am wrong — Gerzzog
– Gerzzog, Commented Dec 12, 2022 at 8:54

Laurenz Albe · Accepted Answer · 2022-12-12 09:00:57Z

5

It doesn't matter. The only time when it helps to ANALYZE a table after creating an index is when the index is on an expression rather than on a plain column. The reason is that PostgreSQL automatically collects statistics for each column, but statistics on expressions are only collected if there is an index or extended statistics on the expression.

answered Dec 12, 2022 at 9:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gerzzog Over a year ago

Thanks, man! Helped much. Now I've understood the theoretic part of this situation

Collectives™ on Stack Overflow

Postgres: Analyze after creating index or before

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related