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.klaver@xxxxxxxxxxx
