On a different machine, we use 12.7. Still same issue On Thu, Jul 8, 2021 at 3:49 PM Allan Barrielle <allan.barrie...@gmail.com> wrote:
> Hello, > > > Is it true that the SELECTs have no joins in them ? > > Yes there is a lot of LEFT JOIN. > > > When it doesn't work, you could check SELECT * FROM pg_stat_activity, > and > >SELECT pg_blocking_pids(pid), * FROM pg_locks, to see what's going on. > > I can't see any blocking queries blocking pg_locks, pg_blocking_pids. > > > It'd be very useful to get "explain analyze" for a working query and for > a > > stuck query. It sound like the stuck query never finishes, so maybe the > second > > part is impossible (?) > > We run an explain analysis and we see some very interesting stuff going on. > It seems without explicitly adding a `ANALYZE`, the query has a cost of > over billions, so the query is not stuck but took forever. > When I run the same scripts with an ANALYZE right before running the > query, the query is exec is 50secondes and the cost is normal > > Explain analyze WITHOUT ANALYZE https://explain.depesz.com/s/RaSr > Explain analyze same query WITH ANALYZE BEFORE > https://explain.depesz.com/s/tYVl > > The configuration is tuned by aws aurora, but this issue happens also with > a default config. > > allow_system_table_mods,off > application_name,DataGrip 2021.1.3 > archive_command,(disabled) > archive_mode,off > archive_timeout,5min > array_nulls,on > authentication_timeout,1min > autovacuum,on > autovacuum_analyze_scale_factor,0.05 > autovacuum_analyze_threshold,50 > autovacuum_freeze_max_age,200000000 > autovacuum_max_workers,12 > autovacuum_multixact_freeze_max_age,400000000 > autovacuum_naptime,5s > autovacuum_vacuum_cost_delay,1ms > autovacuum_vacuum_cost_limit,1200 > autovacuum_vacuum_scale_factor,0.1 > autovacuum_vacuum_threshold,50 > autovacuum_work_mem,-1 > backend_flush_after,0 > backslash_quote,safe_encoding > bgwriter_delay,200ms > bgwriter_flush_after,0 > bgwriter_lru_maxpages,100 > bgwriter_lru_multiplier,2 > bonjour,off > bytea_output,hex > check_function_bodies,on > checkpoint_completion_target,0.9 > checkpoint_flush_after,0 > checkpoint_timeout,15min > checkpoint_warning,30s > client_encoding,UTF8 > client_min_messages,notice > commit_delay,0 > commit_siblings,5 > constraint_exclusion,partition > cpu_index_tuple_cost,0.005 > cpu_operator_cost,0.0025 > cpu_tuple_cost,0.01 > cursor_tuple_fraction,0.1 > DateStyle,"ISO, MDY" > db_user_namespace,off > deadlock_timeout,1s > debug_pretty_print,on > debug_print_parse,off > debug_print_plan,off > debug_print_rewritten,off > default_statistics_target,500 > default_text_search_config,pg_catalog.simple > default_transaction_deferrable,off > default_transaction_isolation,read committed > default_transaction_read_only,off > dynamic_library_path,$libdir > effective_cache_size,4GB > effective_io_concurrency,600 > enable_bitmapscan,on > enable_gathermerge,on > enable_hashagg,on > enable_hashjoin,on > enable_indexonlyscan,on > enable_indexscan,on > enable_material,on > enable_mergejoin,on > enable_nestloop,on > enable_parallel_append,on > enable_parallel_hash,on > enable_partition_pruning,on > enable_partitionwise_aggregate,off > enable_partitionwise_join,off > enable_seqscan,on > enable_sort,on > enable_tidscan,on > escape_string_warning,on > event_source,PostgreSQL > exit_on_error,off > extra_float_digits,3 > force_parallel_mode,off > from_collapse_limit,8 > fsync,off > full_page_writes,off > geqo,on > geqo_effort,5 > geqo_generations,0 > geqo_pool_size,0 > geqo_seed,0 > geqo_selection_bias,2 > geqo_threshold,12 > gin_fuzzy_search_limit,0 > gin_pending_list_limit,4MB > hot_standby,off > hot_standby_feedback,on > huge_pages,try > idle_in_transaction_session_timeout,25min > ignore_checksum_failure,off > ignore_system_indexes,off > IntervalStyle,postgres > jit,off > jit_above_cost,100000 > jit_debugging_support,off > jit_dump_bitcode,off > jit_expressions,on > jit_inline_above_cost,500000 > jit_optimize_above_cost,500000 > jit_profiling_support,off > jit_provider,llvmjit > jit_tuple_deforming,on > join_collapse_limit,8 > lc_monetary,C > lc_numeric,C > lc_time,C > listen_addresses,* > lock_timeout,0 > lo_compat_privileges,off > maintenance_work_mem,2GB > max_connections,100 > max_files_per_process,1000 > max_locks_per_transaction,256 > max_logical_replication_workers,4 > max_parallel_maintenance_workers,12 > max_parallel_workers,12 > max_parallel_workers_per_gather,6 > max_pred_locks_per_page,2 > max_pred_locks_per_relation,-2 > max_pred_locks_per_transaction,64 > max_prepared_transactions,0 > max_replication_slots,10 > max_stack_depth,6MB > max_standby_archive_delay,30s > max_standby_streaming_delay,14s > max_sync_workers_per_subscription,2 > max_wal_senders,0 > max_wal_size,8GB > max_worker_processes,12 > min_parallel_index_scan_size,512kB > min_parallel_table_scan_size,8MB > min_wal_size,2GB > old_snapshot_threshold,-1 > operator_precedence_warning,off > parallel_leader_participation,off > parallel_setup_cost,1000 > parallel_tuple_cost,0.1 > password_encryption,md5 > port,5432 > post_auth_delay,0 > pre_auth_delay,0 > quote_all_identifiers,off > random_page_cost,1.1 > restart_after_crash,on > row_security,on > search_path,public > seq_page_cost,1 > session_replication_role,origin > shared_buffers,1GB > standard_conforming_strings,on > statement_timeout,0 > superuser_reserved_connections,3 > synchronize_seqscans,on > synchronous_commit,on > syslog_facility,local0 > syslog_ident,postgres > syslog_sequence_numbers,on > syslog_split_messages,on > tcp_keepalives_count,9 > tcp_keepalives_idle,7200 > tcp_keepalives_interval,75 > temp_buffers,8MB > temp_file_limit,-1 > TimeZone,UTC > trace_notify,off > trace_recovery_messages,log > trace_sort,off > track_activities,on > track_activity_query_size,4kB > track_commit_timestamp,off > track_counts,on > track_functions,none > track_io_timing,off > transform_null_equals,off > update_process_title,on > vacuum_cleanup_index_scale_factor,0.1 > vacuum_cost_delay,0 > vacuum_cost_limit,200 > vacuum_cost_page_dirty,20 > vacuum_cost_page_hit,1 > vacuum_cost_page_miss,0 > vacuum_defer_cleanup_age,0 > vacuum_freeze_min_age,50000000 > vacuum_freeze_table_age,150000000 > vacuum_multixact_freeze_min_age,5000000 > vacuum_multixact_freeze_table_age,150000000 > wal_buffers,16MB > wal_compression,off > wal_level,minimal > wal_log_hints,off > wal_receiver_status_interval,10s > wal_receiver_timeout,30s > wal_retrieve_retry_interval,5s > wal_sender_timeout,1min > wal_sync_method,fdatasync > wal_writer_delay,200ms > wal_writer_flush_after,1MB > work_mem,2GB > xmlbinary,base64 > xmloption,content > zero_damaged_pages,off > > On Thu, Jul 8, 2021 at 2:33 PM Justin Pryzby <pry...@telsasoft.com> wrote: > >> On Thu, Jul 08, 2021 at 01:00:28AM +0200, Allan Barrielle wrote: >> > All is fine, and can work great. >> > But sometimes, some queries that used to take about 20 secs to complete >> can >> > suddenly end in 5mins. >> > Important all queries have the same shape -> CREATE TABLE SELECT AS *(a >> bit >> > of transform) FROM TABLE). No update, nothing, it’s dead simple. >> > >> > Nothing works, it’s very random, some query won’t simply work ( even >> after >> > hours ). >> >> When it doesn't work, you could check SELECT * FROM pg_stat_activity, and >> SELECT pg_blocking_pids(pid), * FROM pg_locks, to see what's going on. >> >> > Important all queries have the same shape -> CREATE TABLE SELECT AS *(a >> bit >> > of transform) FROM TABLE). No update, nothing, it’s dead simple. >> > We are just trying to copy a table from schema1, to schema2, to schema3 >> and >> > finally schema3. That’s it. >> >> Is it true that the SELECTs have no joins in them ? >> >> Did this ever work better or differently under different versions of >> postgres ? >> >> > Why does the query never end even after hours ? Why there is no log >> about >> > where the query is stuck. >> >> Please send your nondefault config. >> https://wiki.postgresql.org/wiki/Server_Configuration >> >> Also enable logging (I just added this to the wiki). >> https://wiki.postgresql.org/wiki/Slow_Query_Questions#Enable_Logging >> >> It'd be very useful to get "explain analyze" for a working query and for a >> stuck query. It sound like the stuck query never finishes, so maybe the >> second >> part is impossible (?) >> >> But it'd be good to get at least "explain" output. You'd have to edit >> your sql >> script to run an "explain" before each query, and run it, logging the >> ouput, >> until you capture the plan for a stuck query. Save the output and send >> here, >> along with the query plan for a working query. >> >> -- >> Justin >> >
