Re: Performance degradation with CTEs, switching from PG 11 to PG 15
Am 22.11.23 um 12:38 schrieb Jean-Christophe Boggio: Also, adding "materialized" to both "withcwrack" and "withcwrack0" CTEs gets the result in acceptable timings (a few seconds). The problem with this is that we have some clients with older versions of PG and I guess blindly adding the "materialized" keyword will cause errors. yeah, prior to 11 CTEs are a optimizer barrier. You can try to rewrite the queries to not using CTEs - or upgrade. If i were you i would upgrade. Regards, Andreas -- Andreas Kretschmer - currently still (garden leave) Technical Account Manager (TAM) www.enterprisedb.com
Re: simple query running long time within a long transaction.
Am 17.11.23 um 09:10 schrieb James Pang (chaolpan): Hi, We found one simple query manually run very fast(finished in several milliseconds), but there are 2 sessions within long transaction to run same sql with same bind variables took tens of seconds. you try to set plan_cache_mode to force_custom_plan, default is auto and with that and bind variables pg will use a generic plan. Regards, Andreas -- Andreas Kretschmer - currently still (garden leave) Technical Account Manager (TAM) www.enterprisedb.com
Re: [EXTERNAL] Re: Performance down with JDBC 42
Am 05.11.23 um 17:20 schrieb Abraham, Danny: Thanks Laurenz, Traced two huge plans. They differ. The fast one does use Materialize and Memoize (the psql). Is there something in JDBC 42 that blocks these algoruthms? *maybe* the driver changed some settings. You can check it with select name, setting from pg_settings where name ~ 'enable'; using the JDBC-connection. Regards, Andreas Thanks again Danny -Original Message- From: Laurenz Albe Sent: Saturday, November 4, 2023 11:07 PM To: Abraham, Danny ; psql-performance Subject: [EXTERNAL] Re: Performance down with JDBC 42 On Sat, 2023-11-04 at 19:08 +, Abraham, Danny wrote: Asking for help with a JDBC related issue. Environment: Linux 7.9 PG 14.9 , very busy PG Server. A big query - 3 unions and about 10 joins runs : - 70ms on psql , DBeaver with JDBC 42 and in our Server using old JDBC 9.2 - 2500 ms in our Server using new JDBC 42 driver. ( and this is running many times) Question: Is there a structured way to identify optimization setup ( Planner Method s ) changes? Are there any known changes specific to JDBC 42. What I would do is enable auto_explain and look at the execution plan when the statement is run by the JDBC driver. Then you can compare the execution plans and spot the difference. Yours, Laurenz Albe -- Andreas Kretschmer - currently still (garden leave) Technical Account Manager (TAM) www.enterprisedb.com
Re: Planning time is time-consuming
On 11 September 2023 03:15:43 CEST, Laurenz Albe
wrote:
>On Fri, 2023-09-08 at 18:51 +0800, Mikhail Balayan wrote:
>> I have three tables:
>> - test_db_bench_1
>> - test_db_bench_tenants
>> - test_db_bench_tenant_closure
>>
>> And the query to join them:
>> SELECT "test_db_bench_1"."id" id, "test_db_bench_1"."tenant_id"
>> FROM "test_db_bench_1"
>> JOIN "test_db_bench_tenants" AS "tenants_child" ON
>> (("tenants_child"."uuid" = "test_db_bench_1"."tenant_id")
>> AND
>> ("tenants_child"."is_deleted" != true))
>> JOIN "test_db_bench_tenant_closure" AS "tenants_closure" ON
>> (("tenants_closure"."child_id" = "tenants_child"."id")
>> AND
>> ("tenants_closure"."barrier" <= 0))
>> JOIN "test_db_bench_tenants" AS "tenants_parent" ON
>> (("tenants_parent"."id" = "tenants_closure"."parent_id")
>> AND
>> ("tenants_parent"."uuid" IN ('4c79c1c5-21ae-45a0-8734-75d67abd0330'))
>> AND
>> ("tenants_parent"."is_deleted" != true))
>> LIMIT 1
>>
>>
>> With following execution plan:
>>
>>
>> QUERY PLAN
>> --
>> ---
>> Limit (cost=1.56..1.92 rows=1 width=44) (actual time=0.010..0.011 rows=0
>> loops=1)
>> -> Nested Loop (cost=1.56..162.42 rows=438 width=44) (actual
>> time=0.009..0.009 rows=0 loops=1)
>> -> Nested Loop (cost=1.13..50.27 rows=7 width=36) (actual
>> time=0.008..0.009 rows=0 loops=1)
>> -> Nested Loop (cost=0.84..48.09 rows=7 width=8) (actual
>> time=0.008..0.009 rows=0 loops=1)
>> -> Index Scan using test_db_bench_tenants_uuid on
>> test_db_bench_tenants tenants_parent (cost=0.41..2.63 rows=1 width=8)
>> (actual time=0.008..0.008 rows=0 loops=1)
>> Index Cond: ((uuid)::text =
>> '4c79c1c5-21ae-45a0-8734-75d67abd0330'::text)
>> Filter: (NOT is_deleted)
>> -> Index Scan using test_db_bench_tenant_closure_pkey
>> on test_db_bench_tenant_closure tenants_closure (cost=0.42..45.06 rows=40
>> width=16) (never executed)
>> Index Cond: (parent_id = tenants_parent.id)
>> Filter: (barrier <= 0)
>> -> Index Scan using test_db_bench_tenants_pkey on
>> test_db_bench_tenants tenants_child (cost=0.29..0.31 rows=1 width=44)
>> (never executed)
>> Index Cond: (id = tenants_closure.child_id)
>> Filter: (NOT is_deleted)
>> -> Index Scan using test_db_bench_1_idx_tenant_id_3 on
>> acronis_db_bench_heavy (cost=0.43..14.66 rows=136 width=44) (never executed)
>> Index Cond: ((tenant_id)::text = (tenants_child.uuid)::text)
>> Planning Time: 0.732 ms
>> Execution Time: 0.039 ms
>>
>>
>> Where the planning time gets in the way as it takes an order of magnitude
>> more time than the actual execution.
>>
>> Is there a possibility to reduce this time? And, in general, to understand
>> why planning takes so much time.
>
>You could try to VACUUM the involved tables; indexes with many entries
>pointing to dead tuples
>can cause a long planing time.
>
>Also, there are quite a lot of indexes on "test_db_bench_1". On a test
>database, drop some
>indexes and see if that makes a difference.
You can use pg_stat_user_indexes to check if those indexes are in use or not.
>
>Finally, check if "default_statistics_target" is set to a high value, or if
>the "Stats target"
>for some column in the "\d+ tablename" output is set higher than 100.
>
>Yours,
>Laurenz Albe
>
>
Re: Selecting RAM and CPU based on max_connections
On 20 May 2022 10:27:50 CEST, aditya desai wrote: >Hi, >One of our applications needs 3000 max_connections to the database. >Connection pooler like pgbouncer or pgpool is not certified within the >organization yet. So they are looking for setting up high configuration >Hardware with CPU and Memory. Can someone advise how much memory and CPU >they will need if they want max_conenction value=3000. > >Regards, >Aditya. Pgbouncer would be the best solution. CPU: number of concurrent connections. RAM: shared_buffer + max_connections * work_mem + maintenance_mem + operating system + ... -- 2ndQuadrant - The PostgreSQL Support Company
Re: Postgresql 12, 512 partition by hash. Slow select
Am 05.04.20 um 19:48 schrieb Arya F: Am I missing something in my setup? Or is this expected? I do know having more than 100 partitions in prior versions of PostgreSQL 12 would cause a major slow down, but from what I read PostgreSQL 12 addresses that now? to say more about your problem we need to know more. For instance, the exact table definition, the query and the execution plan (explain analyse ...). Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Slow planning time when public schema included (12 vs. 9.4)
Am 21.03.20 um 13:02 schrieb Anders Steinlein: default_statistics_target = 1000 not sure if this be the culprit here, but i think this is way too high. Leave it at the normal value of 100 and raise it only for particular tables and columns. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Query optimization advice for beginners
Am 27.01.20 um 14:15 schrieb Kemal Ortanca: https://explain.depesz.com/s/G4vq the estimates and the real values are very different, seems like problems with autoanalyze. which version? Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Delete huge Table under XFS
Am 19.09.19 um 17:59 schrieb Joao Junior: I have a table that Is not being use anymore, I want to drop it. The table is huge, around 800GB and it has some index on it. When I execute the drop table command it goes very slow, I realised that the problem is the filesystem. It seems that XFS doesn't handle well big files, there are some discussion about it in some lists. PG doesn't create one big file for this table, but about 800 files with 1GB size each. I have to find a way do delete the table in chunks. Why? If you want to delete all rows, just use TRUNCATE. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: improving windows functions performance
Am 05.08.19 um 22:47 schrieb Mariel Cherkassky: Hey, I have a very big query that consist from 3-4 subqueries that use windows functions. There is a chance that I'll need to rewrite the query but first I'm trying to search for other ways to improve it and I'll be happy to hear if one of u have an idea. Basically my table has the following structure : (objid,first_num,last_num) and each record is a range from the first number to the last one for that specific obj. I'm trying to unite ranges that overlaps. For example : for the following table : objid first_num last_num 1 5 7 1 8 10 2 4 6 2 9 10 I would like to get : objid first_num last_num 1 5 10 2 4 6 2 9 10 I have a query that does it but takes about 4s for 1.5M records. I created an index on (objid,first_num,last_num) in order to use only index scan instead of seq scan on this table. I wanted to here if u guys have any other ideas. you should provide more information, for instance: * used version * table-structure * real query * execution plan (using explain analyse) Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Strange runtime partition pruning behaviour with 11.4
Am 03.08.19 um 16:06 schrieb Thomas Kellerer: it's posible to rewrite the query to: test=# explain analyse select count(*) from foo_bar_baz as fbb where foo_id = (select foo_id from foo where foo_name = 'eeny'); I know, that's not a solution, but a workaround. :-( Yes, I discovered that as well. But I'm more confused (or concerned) by the fact that the (original) query works correctly *without* statistics. Thomas can't reproduce that :-( (PG 11.4 Community) (all in a file and executed the explain immediately) Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Strange runtime partition pruning behaviour with 11.4
Hi, Am 03.08.19 um 15:16 schrieb MichaelDBA: I too am a bit perplexed by why runtime partition pruning does not seem to work with this example. Anybody got any ideas of this? please don't top-posting. it's posible to rewrite the query to: test=# explain analyse select count(*) from foo_bar_baz as fbb where foo_id = (select foo_id from foo where foo_name = 'eeny'); QUERY PLAN --- Finalize Aggregate (cost=15880.63..15880.64 rows=1 width=8) (actual time=48.447..48.448 rows=1 loops=1) InitPlan 1 (returns $0) -> Seq Scan on foo (cost=0.00..24.50 rows=6 width=4) (actual time=0.243..0.246 rows=1 loops=1) Filter: ((foo_name)::text = 'eeny'::text) Rows Removed by Filter: 5 -> Gather (cost=15855.92..15856.13 rows=2 width=8) (actual time=48.376..51.468 rows=3 loops=1) Workers Planned: 2 Params Evaluated: $0 Workers Launched: 2 -> Partial Aggregate (cost=14855.92..14855.93 rows=1 width=8) (actual time=42.600..42.600 rows=1 loops=3) -> Parallel Append (cost=0.00..13883.01 rows=389162 width=0) (actual time=0.139..34.914 rows=83500 loops=3) -> Parallel Bitmap Heap Scan on foo_bar_baz_0 fbb (cost=4.23..14.73 rows=6 width=0) (never executed) Recheck Cond: (foo_id = $0) -> Bitmap Index Scan on foo_bar_baz_0_pkey (cost=0.00..4.23 rows=10 width=0) (never executed) Index Cond: (foo_id = $0) -> Parallel Seq Scan on foo_bar_baz_2 fbb_2 (cost=0.00..3865.72 rows=178218 width=0) (never executed) Filter: (foo_id = $0) -> Parallel Seq Scan on foo_bar_baz_1 fbb_1 (cost=0.00..3195.62 rows=147250 width=0) (actual time=0.129..24.735 rows=83500 loops=3) Filter: (foo_id = $0) -> Parallel Seq Scan on foo_bar_baz_3 fbb_3 (cost=0.00..2334.49 rows=107559 width=0) (never executed) Filter: (foo_id = $0) -> Parallel Seq Scan on foo_bar_baz_4 fbb_4 (cost=0.00..1860.95 rows=85756 width=0) (never executed) Filter: (foo_id = $0) -> Parallel Seq Scan on foo_bar_baz_5 fbb_5 (cost=0.00..665.69 rows=30615 width=0) (never executed) Filter: (foo_id = $0) Planning Time: 12.648 ms Execution Time: 52.621 ms (27 rows) test=*# I know, that's not a solution, but a workaround. :-( (pg 12beta2 and also with PostgreSQL 11.4 (2ndQPG 11.4r1.6.7)) Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Searching in varchar column having 100M records
Am 17.07.19 um 14:48 schrieb Tomas Vondra: Either that, or try creating a covering index, so that the query can do an index-only scan. That might reduce the amount of IO against the table, and in the index the data should be located close to each other (same page or pages close to each other). So try something like CREATE INDEX ios_idx ON table (field, user_id); and make sure the table is vacuumed often enough (so that the visibility map is up to date). yeah, and please don't use varchar(64), but instead UUID for the user_id - field to save space on disk and for faster comparison. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Pg10 : Client Configuration for Parallelism ?
Am 17.04.19 um 11:51 schrieb laurent.decha...@orange.com: Here are the logs (with log_error_verbosity = verbose) : 2019-04-17 11:30:42 CEST;35895;thedbuser;thedb;0;LOG: 0: execute : SELECT COUNT(1) FROM big_table 2019-04-17 11:30:42 CEST;35895;thedbuser;thedb;0;LOCATION: exec_execute_message, postgres.c:1959 2019-04-17 11:31:08 CEST;35895;thedbuser;thedb;0;LOG: 0: duration: 25950.908 ms 2019-04-17 11:31:20 CEST;37257;thedbuser;thedb;0;LOG: 0: execute : SELECT COUNT(1) FROM big_table 2019-04-17 11:31:20 CEST;37257;thedbuser;thedb;0;LOCATION: exec_execute_message, postgres.c:1959 2019-04-17 11:31:32 CEST;37257;thedbuser;thedb;0;LOG: 0: duration: 11459.943 ms 2019-04-17 11:32:56 CEST;37324;thedbuser;thedb;0;LOG: 0: statement: SELECT COUNT(1) FROM big_table; 2019-04-17 11:32:56 CEST;37324;thedbuser;thedb;0;LOCATION: exec_simple_query, postgres.c:940 2019-04-17 11:33:08 CEST;37324;thedbuser;thedb;0;LOG: 0: duration: 11334.677 ms That's compareable. The first one took more time, cold cache. The 2nd and 3rd are faster, warm cache. But: we can't see if the execution is paralell or not. If you want to know that, install and use auto_explain. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Pg10 : Client Configuration for Parallelism ?
Am 17.04.19 um 08:30 schrieb laurent.decha...@orange.com:
SELECT current_setting('max_parallel_workers_per_gather') gives 10 from my
session.
Is there a client configuration that prevents from using parallelism ?
unlikely.
if i were you, i would compare all settings, using the different client
software. (show all, and compare)
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
Re: PostgreSQL upgrade.
Am 10.04.19 um 07:40 schrieb Daulat Ram: We have two node postgresql database version 9.6 with streaming replication which is running on docker environment, os Linux (Ubuntu) and we have to migrate on PostgresQL11. I need your suggestions & steps to compete the upgrade process successfully. there are exists several ways to do that. You can take a normal dump and replay it in the new version, you can use pg_upgrade, and you can use a logical replication (using slony, londiste or pg_logical from 2ndQuadrant). There is no 'standard way' to do that, all depends on your requirements and knowledge how to work with that tools. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: endless quere when upsert with ON CONFLICT clause
Am 29.03.19 um 15:29 schrieb Stephan Schmidt: PostgreSQL version: 11.2 Operating system: Linux Description: We have a wuite complex CTE which collects data fast enough for us and has a ok execution plan. When we insert the result into a table like With _/some/_data AS ( SELECT…. ), _/some/_other_data AS ( SELECT …. ) INSERT INTO table1 SELECT * FROM _/some/_other_data ; It works quite well and we are happy with it’s performance (arround 10 seconds). But as soon as we add an ON CONFLICT clause (like below) the queries runs for ages and doesnt seem to stop. We usually terminate it after 12 Hours With _/some/_data AS ( SELECT…. ), _/some/_other_data AS ( SELECT …. ) INSERT INTO table1 SELECT * FROM _/some/_other_data ON CONFLICT (column1, column2) DO UPDATE SET column1 = excluded.columnA, column2 = excluded.columnB, . . . ; Where is the Problem? can you show us the explain (analyse) - plan? i have tried to reproduce, but it seems okay for me. test=*# create table bla (i int primary key, t text); CREATE TABLE test=*# insert into bla select s, 'name ' || s::text from generate_series(1, 10) s; INSERT 0 10 test=*# commit; COMMIT test=*# explain analyse with foo as (select x.* as i from generate_series(1, 1000) x) insert into bla select * from foo on conflict (i) do update set t=excluded.i::text; QUERY PLAN --- Insert on bla (cost=10.00..30.00 rows=1000 width=36) (actual time=16.789..16.789 rows=0 loops=1) Conflict Resolution: UPDATE Conflict Arbiter Indexes: bla_pkey Tuples Inserted: 0 Conflicting Tuples: 1000 CTE foo -> Function Scan on generate_series x (cost=0.00..10.00 rows=1000 width=4) (actual time=0.214..0.443 rows=1000 loops=1) -> CTE Scan on foo (cost=0.00..20.00 rows=1000 width=36) (actual time=0.220..1.124 rows=1000 loops=1) Planning Time: 0.104 ms Execution Time: 16.860 ms (10 rows) test=*# explain analyse with foo as (select x.* + 1000 as i from generate_series(1, 1000) x) insert into bla select * from foo on conflict (i) do update set t=excluded.i::text; QUERY PLAN --- Insert on bla (cost=12.50..32.50 rows=1000 width=36) (actual time=13.424..13.424 rows=0 loops=1) Conflict Resolution: UPDATE Conflict Arbiter Indexes: bla_pkey Tuples Inserted: 1000 Conflicting Tuples: 0 CTE foo -> Function Scan on generate_series x (cost=0.00..12.50 rows=1000 width=4) (actual time=0.079..0.468 rows=1000 loops=1) -> CTE Scan on foo (cost=0.00..20.00 rows=1000 width=36) (actual time=0.081..1.325 rows=1000 loops=1) Planning Time: 0.052 ms Execution Time: 13.471 ms (10 rows) test=*# as you can see, no big difference between the 2 plans. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Zero throughput on a query on a very large table.
Am 25.01.19 um 06:20 schrieb l...@laurent-hasson.com:
Hello,
We have been stuck for the past week on a query that simply won’t
“execute”. We have a table with 1.2B rows that took around 14h to
load, but a simple select takes forever and after 10h, no records are
coming through still.
Environment:
- Table tmp_outpatient_rev with 41 VARCHAR columns
(desy_sort_key, claim_no, clm_line_num, clm_thru_dt, nch_clm_type_cd,
rev_cntr, rev_cntr_dt, …)
- 1.2B rows (Billion with a ‘B’)
- A single Unique Index on columns desy_sort_key, claim_no,
clm_line_num
- select pg_size_pretty(pg_relation_size('tmp_outpatient_rev'))
--> 215GB
- Database Server: 64GB, 8 cores/16 threads, HDDs 10K
- Linux
- PG 11.1
Query:
select * from tmp_outpatient_rev order by desy_sort_key, claim_no
Plan:
Gather Merge (cost=61001461.16..216401602.29 rows=1242732290
width=250)
Output: desy_sort_key, claim_no, clm_line_num, clm_thru_dt,
nch_clm_type_cd, rev_cntr, rev_cntr_dt, …
Workers Planned: 10
-> Sort (cost=61000460.97..61311144.04 rows=124273229 width=250)
Output: desy_sort_key, claim_no, clm_line_num,
clm_thru_dt, nch_clm_type_cd, rev_cntr, rev_cntr_dt, …
Sort Key: tmp_outpatient_rev.desy_sort_key,
tmp_outpatient_rev.claim_no
-> Parallel Seq Scan on public.tmp_outpatient_rev
(cost=0.00..29425910.29 rows=124273229 width=250)
Output: desy_sort_key, claim_no, clm_line_num,
clm_thru_dt, nch_clm_type_cd, rev_cntr, rev_cntr_dt, …
Method of access:
- Using Pentaho Kettle (an ETL tool written in Java and using
JDBC), we simply issue the query and expect records to start streaming
in ASAP.
- Issue was replicated with really basic JDBC code in a Java test
program.
- The database doesn't have much other data and the table was
loaded from a CSV data source with LOAD over something like 14h
(average throughput of about 25K rows/s)
- Settings:
alter database "CMS_TMP" set seq_page_cost=1;
alter database "CMS_TMP" set random_page_cost=4;
alter database "CMS_TMP" set enable_seqscan=true;
JDBC connection string with no extra params.
Database has been generally configured properly.
Problem:
- The plan shows a full table scan followed by a sort, and then a
gather merge. With 1.2B rows, that's crazy to try to sort that
- After 10h, the query is still "silent" and no record is
streaming in. IO is very high (80-90% disk throughput utilization) on
the machine (the sort…).
- I have tried to hack the planner to force an index scan (which
would avoid the sort/gather steps and should start streaming data
right away), in particular, enable_seqscan=false or seq_page_cost=2.
This had ZERO impact on the plan to my surprise.
- I changed the “order by” to include all 3 columns from the index,
or created a non-unique index with only the first 2 columns, all to no
effect whatsoever either.
- The table was written over almost 14h at about 25K row/s and it
seems to me I should be able to read the data back at least as fast.
Why is a simple index scan not used? Why are all our efforts to try to
force the use of the index failing?
the query isn't that simple, there is no where condition, so PG has to
read the whole table and the index is useless. Would it be enought to
select only the columns covered by the index?
(run a vacuum on the table after loading the data, that's can enable a
index-only-scan in this case)
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
Re: Multi-second pauses blocking even trivial activity
Am 21.09.2018 um 21:07 schrieb Patrick Molgaard: Andreas -- just following up to say that this was indeed the root cause. Thanks again. glad i could help you. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Multi-second pauses blocking even trivial activity
> >Intermittently (one or two times a week), all queries on that host are >simultaneously blocked for extended periods (10s of seconds). > >The blocked queries are trivial & not related to locking - I'm seeing >slowlogs of the form: > please check if THP are enabled. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company
Re: trying to delete most of the table by range of date col
Am 03.09.2018 um 09:06 schrieb Justin Pryzby: Note, I believe it's planned in the future for foreign keys to support referenes to partitioned tables, at which point you could just DROP the monthly partition...but not supported right now. the future is close, that's possible in 11 ;-) Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Extremely slow when query uses GIST exclusion index
Am 29.08.2018 um 20:10 schrieb David: On Wed, Aug 29, 2018 at 7:25 AM, Andreas Kretschmer mailto:andr...@a-kretschmer.de>> wrote: Okay, other solution. The problem is the nested loop, we can disable that: test=*# set enable_nestloop to false; Is it OK to keep this off permanently in production? no, but you can switch off/on per session, for instance. and you can it set to on after that query. Nested Loop (cost=319.27..776.18 rows=1 width=196) (actual time=3.156..334.963 rows=1 loops=1) Join Filter: (app.group_id = member_span.group_id) -> Hash Join (cost=319.00..771.00 rows=12 width=104) (actual time=3.100..14.040 rows=1 loops=1) Hm, also, it looks like one of the oddities of this query is that PostgreSQL is severely underestimating the cardinality of the join. ack, that's the main problem here, i think. It leads to the expensive nested loop. Tbh, i don't have a better suggestion now besides the workaround with setting nestloop to off. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Extremely slow when query uses GIST exclusion index
Am 29.08.2018 um 05:31 schrieb David: For now, I can bypass the GIST index by avoiding range operators in my queries. But why is the GIST index so slow? your GiST-Index contains (member_id,group_id,valid_period), but your query is only on the latter 2 fields. test=*# create index test_index on member_span using gist (group_id,valid_period); CREATE INDEX test=*# commit; COMMIT test=# explain analyse SELECT * FROM app JOIN group_span ON app.group_id = group_span.group_id AND app.app_time <@ group_span.valid_period JOIN member_span ON group_span.group_id = member_span.group_id AND group_span.valid_period && member_span.valid_period; QUERY PLAN --- Nested Loop (cost=319.27..776.18 rows=1 width=196) (actual time=3.156..334.963 rows=1 loops=1) Join Filter: (app.group_id = member_span.group_id) -> Hash Join (cost=319.00..771.00 rows=12 width=104) (actual time=3.100..14.040 rows=1 loops=1) Hash Cond: (group_span.group_id = app.group_id) Join Filter: (app.app_time <@ group_span.valid_period) Rows Removed by Join Filter: 2000 -> Seq Scan on group_span (cost=0.00..257.00 rows=12000 width=59) (actual time=0.013..1.865 rows=12000 loops=1) -> Hash (cost=194.00..194.00 rows=1 width=45) (actual time=3.037..3.037 rows=1 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 910kB -> Seq Scan on app (cost=0.00..194.00 rows=1 width=45) (actual time=0.010..1.201 rows=1 loops=1) -> Index Scan using test_index on member_span (cost=0.28..0.42 rows=1 width=92) (actual time=0.027..0.031 rows=1 loops=1) Index Cond: ((group_id = group_span.group_id) AND (group_span.valid_period && valid_period)) Planning time: 2.160 ms Execution time: 335.820 ms (14 rows) test=*# better? Okay, other solution. The problem is the nested loop, we can disable that: test=*# set enable_nestloop to false; SET test=*# explain analyse SELECT * FROM app JOIN group_span ON app.group_id = group_span.group_id AND app.app_time <@ group_span.valid_period JOIN member_span ON group_span.group_id = member_span.group_id AND group_span.valid_period && member_span.valid_period; QUERY PLAN - Hash Join (cost=771.15..1121.33 rows=1 width=196) (actual time=23.291..32.028 rows=1 loops=1) Hash Cond: (member_span.group_id = app.group_id) Join Filter: (group_span.valid_period && member_span.valid_period) Rows Removed by Join Filter: 2000 -> Seq Scan on member_span (cost=0.00..305.00 rows=12000 width=92) (actual time=0.019..1.577 rows=12000 loops=1) -> Hash (cost=771.00..771.00 rows=12 width=104) (actual time=23.254..23.254 rows=1 loops=1) Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1486kB -> Hash Join (cost=319.00..771.00 rows=12 width=104) (actual time=7.968..18.951 rows=1 loops=1) Hash Cond: (group_span.group_id = app.group_id) Join Filter: (app.app_time <@ group_span.valid_period) Rows Removed by Join Filter: 2000 -> Seq Scan on group_span (cost=0.00..257.00 rows=12000 width=59) (actual time=0.010..2.068 rows=12000 loops=1) -> Hash (cost=194.00..194.00 rows=1 width=45) (actual time=7.900..7.900 rows=1 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 910kB -> Seq Scan on app (cost=0.00..194.00 rows=1 width=45) (actual time=0.011..3.165 rows=1 loops=1) Planning time: 1.241 ms Execution time: 32.676 ms (17 rows) test=*# Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Performance
Am 23.02.2018 um 20:29 schrieb Daulat Ram: We have the following requirements in single query or any proper solution. Please help on this. How many sessions are currently opened. ask pg_stat_activity, via select * from pg_stat_activity -and if opened then how many queries have executed on that session. Whot? There isn't a counter for that, AFAIK. -and also we have to trace how much time each query is taking. You can use auto_explain for that -and also we have to find the cost of each query. the same, auto_explain please keep in mind: costs are imaginary. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: OT: Performance of VM
Am 05.02.2018 um 17:22 schrieb Andrew Kerber: Oracle has a problem with transparent hugepages, postgres may well have the same problem, so consider disabling transparent hugepages. yes, that's true. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: OT: Performance of VM
Am 05.02.2018 um 14:14 schrieb Thomas Güttler: What do you suggest to get some reliable figures? sar is often recommended, see https://blog.2ndquadrant.com/in-the-defense-of-sar/. Can you exclude other reasons like vacuum / vacuum freeze? Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: postgresql 10.1 wrong plan in when using partitions bug
Am 04.02.2018 um 13:19 schrieb Mariel Cherkassky:
I checked the plan of the next query :
explain select count(*) from log_full where end_date between
to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
can you rewrite the query to
... where end_date between '2017/12/03' and '2017/12/03'
simple test-case:
test=*# \d+ t
Table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
+--+---+--+-+-+--+-
d | date | | | | plain | |
Partition key: RANGE (d)
Partitions: t_01 FOR VALUES FROM ('2018-02-04') TO ('2018-02-05'),
t_02 FOR VALUES FROM ('2018-02-05') TO ('2018-02-06')
test=*# explain analyse select * from t where d between
to_date('2018/02/04','YY/MM/DD') and to_date('2018/02/04','YY/MM/DD');
QUERY PLAN
-
Append (cost=0.00..122.00 rows=26 width=4) (actual time=0.006..0.006
rows=0 loops=1)
-> Seq Scan on t_01 (cost=0.00..61.00 rows=13 width=4) (actual
time=0.004..0.004 rows=0 loops=1)
Filter: ((d >= to_date('2018/02/04'::text, 'YY/MM/DD'::text))
AND (d <= to_date('2018/02/04'::text, 'YY/MM/DD'::text)))
-> Seq Scan on t_02 (cost=0.00..61.00 rows=13 width=4) (actual
time=0.001..0.001 rows=0 loops=1)
Filter: ((d >= to_date('2018/02/04'::text, 'YY/MM/DD'::text))
AND (d <= to_date('2018/02/04'::text, 'YY/MM/DD'::text)))
Planning time: 0.241 ms
Execution time: 0.042 ms
(7 rows)
test=*# explain analyse select * from t where d between '2018/02/04' and
'2018/02/04';
QUERY PLAN
--
Append (cost=0.00..48.25 rows=13 width=4) (actual time=0.005..0.005
rows=0 loops=1)
-> Seq Scan on t_01 (cost=0.00..48.25 rows=13 width=4) (actual
time=0.004..0.004 rows=0 loops=1)
Filter: ((d >= '2018-02-04'::date) AND (d <= '2018-02-04'::date))
Planning time: 0.203 ms
Execution time: 0.030 ms
(5 rows)
test=*#
maybe the planner should be smart enough to do that for you, but
obvously he can't. So it's a workaround, but it seems to solve the problem.
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
Re: pg_xlog unbounded growth
Hi, Am 24.01.2018 um 12:48 schrieb Stefan Petrea: We're using PostgreSQL 9.6.6 on a Ubuntu 16.04.3 LTS. During some database imports(using pg_restore), we're noticing fast and unbounded growth of pg_xlog up to the point where the partition(280G in size for us) that stores it fills up and PostgreSQL shuts down. The error seen in the logs: 2018-01-17 01:46:23.035 CST [41671] LOG: database system was shut down at 2018-01-16 15:49:26 CST 2018-01-17 01:46:23.038 CST [41671] FATAL: could not write to file "pg_xlog/xlogtemp.41671": No space left on device 2018-01-17 01:46:23.039 CST [41662] LOG: startup process (PID 41671) exited with exit code 1 2018-01-17 01:46:23.039 CST [41662] LOG: aborting startup due to startup process failure 2018-01-17 01:46:23.078 CST [41662] LOG: database system is shut down The config settings I thought were relevant are these ones (but I'm also attaching the entire postgresql.conf if there are other ones that I missed): wal_level=replica archive_command='exit 0;' min_wal_size=2GB max_wal_size=500MB checkpoint_completion_target = 0.7 wal_keep_segments = 8 just to exclude some things out: * is that only happens during pg_restore, or also during normal work? * can you show us how pg_restore is invoked? * how did you create the dump (same pg-version, which format)? * can you change wal_level to minimal? (maybe that's not possible if it is in production und there are standbys) Can you change your archive_command to '/bin/true' ? I'm not sure if that can be the reason for the your problem, but 'exit 0;' terminates the process, but archive_command should return true or false, not terminate. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: 8.2 Autovacuum BUG ?
Am 23.01.2018 um 16:20 schrieb Pavan Teja: Hi David, If it's yes what needs to be done in order to stabilize this issue?? Don't top-post ;-) You can't prevent the generation of wal's (apart from using unlogged tables, but i'm sure, that will be not your solution.) Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: Table with large number of int columns, very slow COPY FROM
On 08.12.2017 05:21, Alex Tokarev wrote: I have made a minimally reproducible test case consisting of a table with 848 columns Such a high number of columns is maybe a sign of a wrong table / database design, why do you have such a lot of columns? How many indexes do you have? Regards, Andreas
