Re: Cannot convert partitioned table to a view
> > The fact that creating a _RETURN rule for a table causes it to be > changed into a view. That's ugly and arguably dangerous. > > Right, thank you. Cheers, -- Kouber Saparev
Different execution plan between PostgreSQL 8.4 and 12.11
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 PostgreSQL 12.11, it returns different execution plan. --PostgreSQL 8.4 --- old=# select count(1) from analyze_word_reports; count - 9164136 (1 row) old=# select indexdef from pg_indexes where tablename='analyze_word_reports'; indexdef --- CREATE INDEX analyze_word_reports_index_cseid ON analyze_word_reports USING btree (cseid) CREATE UNIQUE INDEX analyze_word_reports_pkey ON analyze_word_reports USING btree (seq) (2 rows) old=# explain analyze select 2 from analyze_word_reports where (cseid = 94) limit 1; QUERY PLAN - Limit (cost=0.00..0.63 rows=1 width=0) (actual time=0.893..0.893 rows=0 loops=1) -> Index Scan using analyze_word_reports_index_cseid on analyze_word_reports (cost=0.00..18621.98 rows=29707 width=0) (actual time=0.892..0.892 rows=0 loops=1) Index Cond: (cseid = 94) Total runtime: 0.941 ms (4 rows) --PostgreSQL 12.11 --- new=# select count(1) from analyze_word_reports; count - 20131947 (1 row) new=# select indexdef from pg_indexes where tablename='analyze_word_reports'; indexdef --- CREATE INDEX analyze_word_reports_index_cseid ON analyze_word_reports USING btree (cseid) CREATE UNIQUE INDEX analyze_word_reports_pkey ON analyze_word_reports USING btree (seq) (2 rows) new=# explain analyze select 2 from analyze_word_reports where (cseid = 94) limit 1; QUERY PLAN - Limit (cost=0.00..0.41 rows=1 width=4) (actual time=4908.459..4908.462 rows=1 loops=1) -> Seq Scan on analyze_word_reports (cost=0.00..528610.15 rows=1278561 width=4) (actual time=4908.455..4908.456 rows=1 loops=1) Filter: (cseid = 94) Rows Removed by Filter: 15477750 Planning Time: 0.411 ms Execution Time: 4908.498 ms (6 行) Although PostgreSQL 8.4 has more than 10 million rows of data less than PostgreSQL 12.11, PostgreSQL 12.11 is too slow and analyze_word_reports_index_cseid does not seem to work. I'm guessing that the limit keyword of PostgreSQL 12.11 causes the index not to work. But I don't understand why PostgreSQL 8.4 is normal. What is the reason for this and is there any easy way to maintain compatibility? Regards, -- gzh
Re: Different execution plan between PostgreSQL 8.4 and 12.11
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 Database server (new): PostgreSQL 12.11 64bit I run following sql in PostgreSQL 8.4 and PostgreSQL 12.11, it returns different execution plan. --PostgreSQL 8.4 --- old=# select count(1) from analyze_word_reports; count - 9164136 (1 row) old=# select indexdef from pg_indexes where tablename='analyze_word_reports'; indexdef --- CREATE INDEX analyze_word_reports_index_cseid ON analyze_word_reports USING btree (cseid) CREATE UNIQUE INDEX analyze_word_reports_pkey ON analyze_word_reports USING btree (seq) (2 rows) old=# explain analyze select 2 from analyze_word_reports where (cseid = 94) limit 1; QUERY PLAN - Limit (cost=0.00..0.63 rows=1 width=0) (actual time=0.893..0.893 rows=0 loops=1) -> Index Scan using analyze_word_reports_index_cseid on analyze_word_reports (cost=0.00..18621.98 rows=29707 width=0) (actual time=0.892..0.892 rows=0 loops=1) Index Cond: (cseid = 94) Total runtime: 0.941 ms (4 rows) --PostgreSQL 12.11 --- new=# select count(1) from analyze_word_reports; count - 20131947 (1 row) new=# select indexdef from pg_indexes where tablename='analyze_word_reports'; indexdef --- CREATE INDEX analyze_word_reports_index_cseid ON analyze_word_reports USING btree (cseid) CREATE UNIQUE INDEX analyze_word_reports_pkey ON analyze_word_reports USING btree (seq) (2 rows) new=# explain analyze select 2 from analyze_word_reports where (cseid = 94) limit 1; QUERY PLAN - Limit (cost=0.00..0.41 rows=1 width=4) (actual time=4908.459..4908.462 rows=1 loops=1) -> Seq Scan on analyze_word_reports (cost=0.00..528610.15 rows=1278561 width=4) (actual time=4908.455..4908.456 rows=1 loops=1) Filter: (cseid = 94) Rows Removed by Filter: 15477750 Planning Time: 0.411 ms Execution Time: 4908.498 ms (6 行) Although PostgreSQL 8.4 has more than 10 million rows of data less than PostgreSQL 12.11, PostgreSQL 12.11 is too slow and analyze_word_reports_index_cseid does not seem to work. I'm guessing that the limit keyword of PostgreSQL 12.11 causes the index not to work. But I don't understand why PostgreSQL 8.4 is normal. What is the reason for this and is there any easy way to maintain compatibility? Regards, -- gzh -- Angular momentum makes the world go 'round.
recovery.conf and archive files
I have primary and standby replication setup. On my primary the archive directory is rather large (30GB) and growing. On my standby I have recovery.conf which has archive_cleanup_command = 'pg_archivecleanup -d /var/lib/pgsql/11/data/archive %r' I was under the impression this line would remove data from my primary AND standby. Is that not the case? -- --- Get your facts first, then you can distort them as you please.--
Re: recovery.conf and archive files
Hi, Le dim. 9 oct. 2022 à 13:54, Rita a écrit : > I have primary and standby replication setup. > > On my primary the archive directory is rather large (30GB) and growing. On > my standby I have recovery.conf which has > archive_cleanup_command = 'pg_archivecleanup -d > /var/lib/pgsql/11/data/archive %r' > > I was under the impression this line would remove data from my primary AND > standby. Is that not the case? > > pg_archivecleanup will clean up the *local* directory. It won't clean up the archive directory if it's stored on the primary. If I misunderstood your issue, it would be great to send us the postgresql.conf file from your primary. -- Guillaume.
Re: Different execution plan between PostgreSQL 8.4 and 12.11
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. regards, tom lane
Re: Different execution plan between PostgreSQL 8.4 and 12.11
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 PostgreSQL 8.4 and PostgreSQL 12.11, it returns different execution plan. --PostgreSQL 8.4 --- old=# select count(1) from analyze_word_reports; count - 9164136 (1 row) old=# select indexdef from pg_indexes where tablename='analyze_word_reports'; indexdef --- CREATE INDEX analyze_word_reports_index_cseid ON analyze_word_reports USING btree (cseid) CREATE UNIQUE INDEX analyze_word_reports_pkey ON analyze_word_reports USING btree (seq) (2 rows) old=# explain analyze select 2 from analyze_word_reports where (cseid = 94) limit 1; QUERY PLAN - Limit (cost=0.00..0.63 rows=1 width=0) (actual time=0.893..0.893 rows=0 loops=1) -> Index Scan using analyze_word_reports_index_cseid on analyze_word_reports (cost=0.00..18621.98 rows=29707 width=0) (actual time=0.892..0.892 rows=0 loops=1) Index Cond: (cseid = 94) Total runtime: 0.941 ms (4 rows) --PostgreSQL 12.11 --- new=# select count(1) from analyze_word_reports; count - 20131947 (1 row) new=# select indexdef from pg_indexes where tablename='analyze_word_reports'; indexdef --- CREATE INDEX analyze_word_reports_index_cseid ON analyze_word_reports USING btree (cseid) CREATE UNIQUE INDEX analyze_word_reports_pkey ON analyze_word_reports USING btree (seq) (2 rows) new=# explain analyze select 2 from analyze_word_reports where (cseid = 94) limit 1; QUERY PLAN - Limit (cost=0.00..0.41 rows=1 width=4) (actual time=4908.459..4908.462 rows=1 loops=1) -> Seq Scan on analyze_word_reports (cost=0.00..528610.15 rows=1278561 width=4) (actual time=4908.455..4908.456 rows=1 loops=1) Filter: (cseid = 94) Rows Removed by Filter: 15477750 Planning Time: 0.411 ms Execution Time: 4908.498 ms (6 行) Although PostgreSQL 8.4 has more than 10 million rows of data less than PostgreSQL 12.11, PostgreSQL 12.11 is too slow and analyze_word_reports_index_cseid does not seem to work. I'm guessing that the limit keyword of PostgreSQL 12.11 causes the index not to work. But I don't understand why PostgreSQL 8.4 is normal. What is the reason for this and is there any easy way to maintain compatibility? Regards, -- gzh Well, as someone who has worked on more than one database, I can tell you that new version always means new plans. Most of the time, the new plans are better but sometimes they're not. Your problem is probably caused by one or two SQL statements that have changed plans. I would advise installing pg_hint_plan extension and fixing those few SQL queries manually. PostgreSQL would probably benefit from something like Oracle baselines, which are a good mechanism for carrying plans over to the new version. If you don't want to install the new extension, you can try by running vacuum analyze on the database. Also, upgrading to PgSQL 12 doesn't make much sense given the fact that PgSQL 15 will be released in a few weeks. Can you upgrade to PgSQL 14? Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Logical replication/publication question
Hi, We're migrating from 9.6 to 14, using pglogical. We have several logical slots on the 9.6 instance implementing a change data capture pattern. For the migration, we plan on recreating the slots on the 14 instance, without taking a snapshot of the data. When the migration happens, we will simply start using the slots on the 14 instance (with the understanding that the LSNs won't match between the 9.6 and 14 instances). In testing, we have this working, but there was a wrinkle and I'd like to know if my understanding is correct. On the 9.6 instance, when creating the replication slots, we would use the START_REPLICATION SLOT command, and then immediately take a snapshot, and it has worked great for years. On a 14 instance, receiving changes from the 9.6 instance via pglogical, if we recreate the logical slot, but not take a snapshot, no changes are propagated, and there are no errors. However, if we first issue a 'CREATE PUBLICATION' command, then changes are propagated to the slot as expected. We never issued a 'CREATE PUBLICATION' command on the 9.6 instance. My guess is that the act of taking a snapshot twittled whatever bits were necessary to propagate changes through the slot. By not taking a snapshot on the 14 instance, that doesn't happen, hence the need for the 'CREATE PUBLICATION' command. Alternatively, something was changed between 9.6 and 14 that now requires the new command (I looked through the docs and could find no mention, however). Is my understanding correct? Am I missing something? I just want to make sure I'm not screwing something up. Thanks, Mark
Re:Re: Different execution plan between PostgreSQL 8.4 and 12.11
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, 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. > > regards, tom lane
Re: Different execution plan between PostgreSQL 8.4 and 12.11
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 --- that is, how many rows actually have cseid = 94? If the estimate is far off, then increasing the table's statistics target might help. Another thing that would be worth checking is whether "set enable_seqscan = off" prods it to choose the plan you want. If not, then there's something else going on besides poor estimates. regards, tom lane
