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@xxxxxxxxxxxxx> wrote: >gzh <gzhcoder@xxxxxxx> 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
