From: gzh Sent: Wednesday, 12 October 2022 9:30 PM
> Who can tell me which solution is better below：
> Solution 1: Change the configuration parameters
>set enable_seqscan = off
> Solution 2: Add DISTINCT clause to SQL
>explain analyze select DISTINCT 2 from analyze_word_reports where
Hi everyone,
Who can tell me which solution is better below：
Solution 1: Change the configuration parameters
set enable_seqscan = off
Solution 2: Add DISTINCT clause to SQL
explain analyze select DISTINCT 2 from analyze_word_reports where (cseid =
94) limit 1;
If I
On Wed, 12 Oct 2022 at 13:06, Klint Gore wrote:
> Limit (cost=0.56..28349.28 rows=1 width=4) (actual time=0.039..0.040 rows=1
> loops=1)
> -> Unique (cost=0.56..28349.28 rows=1 width=4) (actual time=0.039..0.039
> rows=1 loops=1)
> -> Index Only Scan using idx on tbl (cost=0.56..2
From: Bruce Momjian Sent: Wednesday, 12 October 2022 1:30 AM
>On Tue, Oct 11, 2022 at 09:59:43AM -0400, Tom Lane wrote:
>> David Rowley writes:
>> > It feels like something is a bit lacking in our cost model here. I'm
>> > just not sure what that is.
>>
>> The ex
On Tue, Oct 11, 2022 at 09:59:43AM -0400, Tom Lane wrote:
> David Rowley writes:
> > It feels like something is a bit lacking in our cost model here. I'm
> > just not sure what that is.
>
> The example you show is the same old problem that we've understood for
> decades: for cost-estimation purpo
David Rowley writes:
> It feels like something is a bit lacking in our cost model here. I'm
> just not sure what that is.
The example you show is the same old problem that we've understood for
decades: for cost-estimation purposes, we assume that matching rows
are more or less evenly distributed
On Tue, 11 Oct 2022 at 16:13, gzh wrote:
> new=# explain analyze select 2 from analyze_word_reports where (cseid = 94)
> limit 1;
> Limit (cost=0.00..0.43 rows=1 width=4) (actual time=2156.964..2156.966
> rows=1 loops=1)
>-> Seq Scan on analyze_word_reports (cost=0.00..528550.75 rows=122
On Tue, 11 Oct 2022 at 22:52, Josef Šimánek wrote:
> This can make some queries fail since there will be no way to gather
> data without seqscan.
Disabling enable_seqscan only adds a const penalty to Seq Scans. It
does not outright disallow them altogether.
Having said that, having Paths with th
út 11. 10. 2022 v 11:17 odesílatel gzh napsal:
>
> Thank you for all your assistance.
>
>
> By communicating with my customer, we have adopted the following solution to
> fix the problem.
>
>
> set enable_seqscan = off
>
This can make some queries fail since there will be no way to gather
data wi
Thank you for all your assistance.
By communicating with my customer, we have adopted the following solution to
fix the problem.
set enable_seqscan = off
At 2022-10-11 16:21:42, "Pavel Stehule" wrote:
út 11. 10. 2022 v 10:01 odesílatel gzh napsal:
Thank you for pro
út 11. 10. 2022 v 10:01 odesílatel gzh napsal:
> Thank you for providing the requested information.
>
>
> The WebSite has been used for many years, and this upgrade is only a
> version upgrade of the PostgreSQL database.
> My customer does not want to modify the SQL because it will increase the
>
Thank you for providing the requested information.
The WebSite has been used for many years, and this upgrade is only a version
upgrade of the PostgreSQL database.
My customer does not want to modify the SQL because it will increase the cost
of the project(All SQL that contains a LIMIT claus
út 11. 10. 2022 v 7:08 odesílatel gzh napsal:
> Hi, Pavel
>
>
> > The LIMIT clause changes total cost. This is a very aggressive clause.
> And
>
> > although it is absolutely useless in this case, Postgres does not have
> any
>
> > logic for removing it. Postgres doesn't try to fix developer's m
Hi, Pavel
> The LIMIT clause changes total cost. This is a very aggressive clause. And
> although it is absolutely useless in this case, Postgres does not have any
> logic for removing it. Postgres doesn't try to fix developer's mistakes.
Sorry,I didn't understand what you mean.
Couldn't t
út 11. 10. 2022 v 6:05 odesílatel gzh napsal:
>
> Hi, Pavel
>
> Thank you for your reply.
>
>
> > the LIMIT clause is in this case totally useless and messy, and maybe can
>
> > negative impacts optimizer
>
> Yes. After removing the LIMIT clause, the performance is improved.
>
> The execution pla
Hi, Pavel
Thank you for your reply.
> the LIMIT clause is in this case totally useless and messy, and maybe can
> negative impacts optimizer
Yes. After removing the LIMIT clause, the performance is improved.
The execution plan shows that the index worked.
We've noticed it, but I don't
út 11. 10. 2022 v 5:13 odesílatel gzh napsal:
> Hi, Tom
> Thank you for your reply.
>
> > When you're asking for help, please don't give us vague statements
>
> > like "doesn't seem to work".
>
> I understand.
>
>
> > Did the plan (including rowcount
>
> > estimates) change at all? To what? How
Hi, Tom
Thank you for your reply.
> When you're asking for help, please don't give us vague statements
> like "doesn't seem to work".
I understand.
> Did the plan (including rowcount
> estimates) change at all? To what? How far off is that rowcount
> estimate, anyway --- that is, how m
gzh writes:
> I've run analyze(not vacuum analyze), but it doesn't seem to work.
When you're asking for help, please don't give us vague statements
like "doesn't seem to work". Did the plan (including rowcount
estimates) change at all? To what? How far off is that rowcount
estimate, anyway --
Hi Tom,
Thank you for your prompt response.
I've run analyze(not vacuum analyze), but it doesn't seem to work.
Is there any other way to optimize the database?
At 2022-10-10 00:02:09, "Tom Lane" wrote:
>gzh writes:
>> I run following sql in PostgreSQL 8.4 and PostgreSQL 12.11, i
On 10/9/22 05:11, 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.
Database server (old): PostgreSQL 8.4 32bit
Database server (new): PostgreSQL 12.11 64bit
I run following sql in P
gzh writes:
> I run following sql in PostgreSQL 8.4 and PostgreSQL 12.11, it returns
> different execution plan.
Have you done an ANALYZE in the new database? The rowcount estimates
seem a lot different, leading me to guess that the newer installation
doesn't have good statistics yet.
Did you analyze and vacuum all of the tables in the new database?
On 10/9/22 04:11, 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.
Database server (old): PostgreSQL 8.4 32bit
Datab
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.4 32bit
Database server (new): PostgreSQL 12.11 64bit
I run following sql in PostgreSQL 8.4 and Postgre
24 matches
Mail list logo
