Re:Re: Different execution plan between PostgreSQL 8.2 and 12.5

Dear Tom,

PostgreSQL 8.2

----------------

[root@PostgreSQL8 ~]# cat /etc/redhat-release

CentOS release 6.6 (Final)

[root@PostgreSQL8 ~]# locale | grep LANG

LANG=ja_JP.UTF-8

PostgreSQL 12.5

----------------

[root@PostgreSQL12 ~]# cat /etc/redhat-release

Red Hat Enterprise Linux release 8.5 (Ootpa)

[root@PostgreSQL12 ~]# locale | grep LANG

LANG=ja_JP.utf8

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

--SQL

explain analyze select 

  crew_base.crewid

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';

--PostgreSQL 8.2

---------------

QUERY PLAN

Limit  (cost=0.00..229939.53 rows=7921 width=10) (actual time=2.137..4598.114 rows=4489 loops=1)

  ->  Nested Loop Left Join  (cost=0.00..229939.53 rows=7921 width=10) (actual time=2.136..4597.484 rows=4489 loops=1)

        ->  Seq Scan on crew_base  (cost=0.00..165072.69 rows=7921 width=20) (actual time=2.131..4348.423 rows=4489 loops=1)

              Filter: (status = 1)

        ->  Index Scan using crew_base_crewid_index on crew_base crew_base_introduced  (cost=0.00..8.18 rows=1 width=10) (actual time=0.055..0.055 rows=0 loops=4489)

              Index Cond: (crew_base.introduced_by = crew_base_introduced.crewid)

Total runtime: 4599.985 ms

--PostgreSQL 12.5

---------------

QUERY PLAN

Limit  (cost=1000.00..119573.46 rows=4759 width=7) (actual time=0.996..633.557 rows=4489 loops=1)

  ->  Gather  (cost=1000.00..119573.46 rows=4759 width=7) (actual time=0.994..633.043 rows=4489 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Parallel Seq Scan on crew_base  (cost=0.00..118097.56 rows=1983 width=7) (actual time=0.573..611.579 rows=1496 loops=3)

              Filter: (status = 1)

              Rows Removed by Filter: 493589

Planning Time: 15.966 ms

Execution Time: 634.035 ms

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.

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';

At 2022-08-18 11:32:22, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>gzh <gzhcoder@126.com> writes:
>> I run following sql in PostgreSQL 8.2 and PostgreSQL 12.5, it returns different execution plan.
>
>8.2 is ... well, not stone age maybe, but pretty durn ancient.
>You really ought to update a bit more often than that.  (And
>maybe pay more attention to staying up to date with minor releases?
>Whatever was your reasoning for choosing 12.5, when the latest 12.x
>release is 12.12?)
>
>The 12.5 plan looks like it thinks that the join condition is not
>hashable --- and probably not mergeable as well, else it would have
>done a mergejoin.  This is odd if we assume that the lower()
>outputs are just text.  But you haven't said anything about the
>data types involved, nor what locale setting you're using, nor
>what nondefault settings or extensions you might be using, so
>speculation about the cause would just be speculation.
>
>There is some advice here about how to ask this sort of
>question in a way that would obtain useful answers:
>
>https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
>			regards, tom lane