Re:Re: Different execution plan between PostgreSQL 8.2 and 12.5 - Mailing list pgsql-general

From gzh
Subject Re:Re: Different execution plan between PostgreSQL 8.2 and 12.5
Date
Msg-id 7ae71978.5e6a.182b05c4ab7.Coremail.gzhcoder@126.com
Whole thread Raw
In response to Re: Different execution plan between PostgreSQL 8.2 and 12.5  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Different execution plan between PostgreSQL 8.2 and 12.5
List pgsql-general

Dear Tom,


Thanks for your reply.
Please refer to the information below:


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

pgsql-general by date:

Previous
From: Vijaykumar Jain
Date:
Subject: Re: [(catch-ext)] Re: Setting up streaming replication on large database (20+ TB) for the first time
Next
From: milist ujang
Date:
Subject: ERROR: catalog is missing 3 attribute(s) for relid 150243