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?

CodePudding user response：

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.