Thread: Different execution plan between PostgreSQL 8.2 and 12.5
Hi,
I have had a Perl Website working for 7 years and have had no problems
until a few weeks ago I replaced my database server with a newer one.
Database server (old): PostgreSQL 8.2 32bit
Database server (new): PostgreSQL 12.5 64bit
I run following sql in PostgreSQL 8.2 and PostgreSQL 12.5, it returns different execution plan.
--SQL
explain 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=188628.24..189521.23 rows=10000 width=10)
-> Hash Left Join (cost=188628.24..3800200.71 rows=40443494 width=10)
Hash Cond: (lower(crew_base.introduced_by) = lower(crew_base_introduced.crewid))
-> Seq Scan on crew_base (cost=0.00..165072.69 rows=5446 width=20)
Filter: (status = 1)
-> Hash (cost=161359.55..161359.55 rows=1485255 width=10)
-> Seq Scan on crew_base crew_base_introduced (cost=0.00..161359.55 rows=1485255 width=10)
--PostgreSQL 12.5
---------------
QUERY PLAN
Limit (cost=0.43..47861.44 rows=10000 width=7)
-> Nested Loop Left Join (cost=0.43..169386135.30 rows=35391255 width=7)
Join Filter: (lower(crew_base.introduced_by) = lower(crew_base_introduced.crewid))
-> Seq Scan on crew_base (cost=0.00..128942.75 rows=4759 width=14)
Filter: (status = 1)
-> Materialize (cost=0.43..51909.70 rows=1487340 width=7)
-> Index Only Scan using crew_base_crewid_index on crew_base crew_base_introduced (cost=0.43..38663.00 rows=1487340 width=7)
PostgreSQL 8.2 quickly queried the data, but PostgreSQL 12.5 has not responded.
I'm guessing that the lower() function of PostgreSQL 12.5 invalidates the index.
But I don't understand why PostgreSQL 8.2 is normal.
What is the reason for this and is there any easy way to maintain compatibility?
Regards,
--
gzh
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
On Thu, 18 Aug 2022 at 15:32, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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. In addition to that, I couldn't help notice that the quoted SQL does not seem to belong to the explain. The EXPLAIN has a Limit node, but the query does not. I'm assuming this isn't due to the relations being views since we don't pull up subqueries with a LIMIT. The costs for the 12.5 are cheaper than 8.4's, so I imagine the more likely cause is the planner favouring an early startup plan. It's probably more likely that lower() is providing the planner with bad estimates and there's likely far less than the expected rows, resulting in the LIMIT 10000 being a much larger proportion of the total rows than the planner expects. David
On 8/17/22 20:01, gzh wrote: > Hi, > > > I have had a Perl Website working for 7 years and have had no problems > > until a few weeks ago I replaced my database server with a newer one. Did you run ANALYZE on the 12.5 server after restoring the data to it? > > gzh > > -- Adrian Klaver adrian.klaver@aklaver.com
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
>In addition to that, I couldn't help notice that the quoted SQL does >not seem to belong to the explain. The EXPLAIN has a Limit node, but >the query does not. I'm assuming this isn't due to the relations being >views since we don't pull up subqueries with a LIMIT.
LIMIT node because I connect PostgreSQL with A5M2, the tool will add LIMIT when outputting the execution plan, please ignore it.
At 2022-08-18 11:38:58, "David Rowley" <dgrowleyml@gmail.com> wrote: >On Thu, 18 Aug 2022 at 15:32, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> 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. > >In addition to that, I couldn't help notice that the quoted SQL does >not seem to belong to the explain. The EXPLAIN has a Limit node, but >the query does not. I'm assuming this isn't due to the relations being >views since we don't pull up subqueries with a LIMIT. > >The costs for the 12.5 are cheaper than 8.4's, so I imagine the more >likely cause is the planner favouring an early startup plan. > >It's probably more likely that lower() is providing the planner with >bad estimates and there's likely far less than the expected rows, >resulting in the LIMIT 10000 being a much larger proportion of the >total rows than the planner expects. > >David
At 2022-08-18 12:18:31, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote: >On 8/17/22 20:01, gzh wrote: >> Hi, >> >> >> I have had a Perl Website working for 7 years and have had no problems >> >> until a few weeks ago I replaced my database server with a newer one. > >Did you run ANALYZE on the 12.5 server after restoring the data to it? > > >> >> gzh >> >> > > >-- >Adrian Klaver >adrian.klaver@aklaver.com
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@aklaver.com
Dear Adrian,
I appreciate your reply. Your reply gave me a new idea,
it should not be the problem that the lower() function causes the unique index to fail.
I checked the postgresql.conf file and found that shared_buffers, work_mem and maintenance_work_mem are default value,
but in the postgresql.conf file of PostgreSQL 8.4, the value of these parameters are very large.
When I changed the value of these parameters to a larger value, the problem solved.
At 2022-08-19 12:42:54, "gzh" <gzhcoder@126.com> wrote:
Dear Adrian,
I appreciate your reply. Your reply gave me a new idea,
it should not be the problem that the lower() function causes the unique index to fail.
I checked the postgresql.conf file and found that shared_buffers, work_mem and maintenance_work_mem are default value,
but in the postgresql.conf file of PostgreSQL 8.4, the value of these parameters are very large.
When I changed the value of these parameters to a larger value, the problem solved.