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" <t...@sss.pgh.pa.us> wrote:
>gzh <gzhco...@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
