Re: Different execution plan between PostgreSQL 8.2 and 12.5

On 8/18/22 02:50, gzh wrote:
> Dear Tom,
>
>
> Thanks for your reply.
> Please refer to the information below：
>
>

> I run following sql in PostgreSQL 8.2 and PostgreSQL 12.5, it returns
> different execution plan.

There are ~13 years of improvements to the planner and the database as a
whole, I would more surprised if the execution plans where the same.

>
>
> The execution plan shows that PostgreSQL 12.5 takes less time,
>
> but the data can not display, and SQL has been in a suspended state.

Per:

https://www.postgresql.org/docs/current/sql-explain.html

"This command displays the execution plan that the PostgreSQL planner
generates for the supplied statement. The execution plan shows how the
table(s) referenced by the statement will be scanned — by plain
sequential scan, index scan, etc. — and if multiple tables are
referenced, what join algorithms will be used to bring together the
required rows from each input table."

...

"Keep in mind that the statement is actually executed when the ANALYZE
option is used. Although EXPLAIN will discard any output that a SELECT
would return, ..."

>
> When I change the select clause to the following( crew_base.crewid →
> count(*) ), I can retrieve the number of data rows.
>
> The amount of data in the crew_base table is 1485255.
>
> The data type of the crew_base.crewid field is text.
>
> The crew_base.crewid field has a unique index: CREATE UNIQUE INDEX
> crew_base_crewid_index ON public.crew_base USING btree (crewid)
>
>
> select
>
>   count(*)
>
> from crew_base
>
>      left join crew_base as crew_base_introduced on
> crew_base.introduced_by=crew_base_introduced.crewid
>
> where crew_base.status = '1';
>
>
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com