Обсуждение: Re: PostgreSQL vs oracle doing 1 million sqrts am I doing it wrong?
Thanks to all for the great info. We are new to postgresql and this discussion has both instructed us and increased ourrespect for the database and the community. I am seeing a behavior that I don’t understand and hopefully you guys can clear it up. I am using AWS postgresql db.m3.2xlarge and using pgadmin III 1.18 comparing against AWS oracle on db.m3.2xlarge using sqldeveloper and TOAD. I am running a query with 30 tables in the from clause, getting 137 columns back (this is our most basic query, they geta lot more more complex). It returns back 4800 rows. In oracle 1st run takes 3.92 seconds, 2nd .38 seconds. Scrolling to end takes and extra 1.5 seconds for total of 5.5. Using pgadmin, I run the query. Looking at the lower right hand I can see the time going up. It stops at 8200 ms or closeto it every time, then it takes an extra 6 seconds before it displays the rows on the screen. 2nd, 3rd, etc. runs alltake about same amount of time 8 sec plus 6 sec I then changed it to return only 1 column back. In oracle/sqldeveloper identical behavior as before, same time. In postgresqlit now goes down to 1.8 seconds for 1st, 2nd, etc. runs. I then change it so that I am asking for the sum of 1 column. In oracle time goes down to .2 seconds and postgresql nowgoes down to .2 seconds also. I then change it back to get the full result set and behavior goes back to original, oracle .38 since its cached, postgresql8 seconds. Of the 30 tables 6 are 10-50 gigs in size. Our setting are "shared_buffers";"7639832kB" "effective_cache_size";"15279664kB" "allow_system_table_mods";"off" "application_name";"pgAdmin III - Query Tool" "archive_command";"/etc/rds/dbbin/pgscripts/rds_wal_archive %p" "archive_mode";"on" "archive_timeout";"5min" "array_nulls";"on" "authentication_timeout";"1min" "autovacuum";"on" "autovacuum_analyze_scale_factor";"0.1" "autovacuum_analyze_threshold";"50" "autovacuum_freeze_max_age";"200000000" "autovacuum_max_workers";"3" "autovacuum_multixact_freeze_max_age";"400000000" "autovacuum_naptime";"1min" "autovacuum_vacuum_cost_delay";"20ms" "autovacuum_vacuum_cost_limit";"-1" "autovacuum_vacuum_scale_factor";"0.2" "autovacuum_vacuum_threshold";"50" "backslash_quote";"safe_encoding" "bgwriter_delay";"200ms" "bgwriter_lru_maxpages";"100" "bgwriter_lru_multiplier";"2" "block_size";"8192" "bonjour";"off" "bonjour_name";"" "bytea_output";"escape" "check_function_bodies";"on" "checkpoint_completion_target";"0.9" "checkpoint_segments";"16" "checkpoint_timeout";"5min" "checkpoint_warning";"30s" "client_encoding";"UNICODE" "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_assertions";"off" "debug_pretty_print";"on" "debug_print_parse";"off" "debug_print_plan";"off" "debug_print_rewritten";"off" "default_statistics_target";"100" "default_tablespace";"" "default_text_search_config";"pg_catalog.simple" "default_transaction_deferrable";"off" "default_transaction_isolation";"read committed" "default_transaction_read_only";"off" "default_with_oids";"off" "effective_cache_size";"15279664kB" "effective_io_concurrency";"1" "enable_bitmapscan";"on" "enable_hashagg";"on" "enable_hashjoin";"on" "enable_indexonlyscan";"on" "enable_indexscan";"on" "enable_material";"on" "enable_mergejoin";"on" "enable_nestloop";"on" "enable_seqscan";"on" "enable_sort";"on" "enable_tidscan";"on" "escape_string_warning";"on" "event_source";"PostgreSQL" "exit_on_error";"off" "extra_float_digits";"0" "from_collapse_limit";"8" "fsync";"on" "full_page_writes";"on" "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" "hot_standby";"off" "hot_standby_feedback";"off" "ignore_checksum_failure";"off" "ignore_system_indexes";"off" "integer_datetimes";"on" "IntervalStyle";"postgres" "join_collapse_limit";"8" "krb_caseins_users";"off" "krb_srvname";"postgres" "lc_collate";"en_US.UTF-8" "lc_ctype";"en_US.UTF-8" "lc_messages";"" "lc_monetary";"C" "lc_numeric";"C" "lc_time";"C" "listen_addresses";"*" "lo_compat_privileges";"off" "local_preload_libraries";"" "lock_timeout";"0" "log_autovacuum_min_duration";"-1" "log_checkpoints";"on" "log_connections";"off" "log_destination";"stderr" "log_disconnections";"off" "log_duration";"off" "log_error_verbosity";"default" "log_executor_stats";"off" "log_file_mode";"0644" "log_hostname";"on" "log_line_prefix";"%t:%r:%u@%d:[%p]:" "log_lock_waits";"off" "log_min_duration_statement";"-1" "log_min_error_statement";"error" "log_min_messages";"warning" "log_parser_stats";"off" "log_planner_stats";"off" "log_rotation_age";"1h" "log_rotation_size";"10MB" "log_statement";"none" "log_statement_stats";"off" "log_temp_files";"-1" "log_timezone";"UTC" "log_truncate_on_rotation";"off" "logging_collector";"on" "maintenance_work_mem";"16MB" "max_connections";"2486" "max_files_per_process";"1000" "max_function_args";"100" "max_identifier_length";"63" "max_index_keys";"32" "max_locks_per_transaction";"64" "max_pred_locks_per_transaction";"64" "max_prepared_transactions";"0" "max_stack_depth";"6MB" "max_standby_archive_delay";"30s" "max_standby_streaming_delay";"30s" "max_wal_senders";"0" "password_encryption";"on" "port";"5432" "post_auth_delay";"0" "pre_auth_delay";"0" "quote_all_identifiers";"off" "random_page_cost";"4" "restart_after_crash";"on" "search_path";""$user",public" "segment_size";"1GB" "seq_page_cost";"1" "server_encoding";"UTF8" "server_version";"9.3.3" "server_version_num";"90303" "session_replication_role";"origin" "shared_buffers";"7639832kB" "sql_inheritance";"on" "ssl";"on" "ssl_ca_file";"/rdsdbdata/rds-metadata/ca-cert.pem" "ssl_cert_file";"/rdsdbdata/rds-metadata/server-cert.pem" "ssl_crl_file";"" "ssl_key_file";"/rdsdbdata/rds-metadata/server-key.pem" "ssl_renegotiation_limit";"512MB" "standard_conforming_strings";"on" "statement_timeout";"0" "superuser_reserved_connections";"3" "synchronize_seqscans";"on" "synchronous_commit";"on" "synchronous_standby_names";"" "syslog_facility";"local0" "syslog_ident";"postgres" "tcp_keepalives_count";"2" "tcp_keepalives_idle";"300" "tcp_keepalives_interval";"30" "temp_buffers";"8MB" "temp_file_limit";"-1" "temp_tablespaces";"" "TimeZone";"UTC" "timezone_abbreviations";"Default" "trace_notify";"off" "trace_recovery_messages";"log" "trace_sort";"off" "track_activities";"on" "track_activity_query_size";"1024" "track_counts";"on" "track_functions";"none" "track_io_timing";"off" "transaction_deferrable";"off" "transaction_isolation";"read committed" "transaction_read_only";"off" "transform_null_equals";"off" "unix_socket_group";"rdsdb" "unix_socket_permissions";"0700" "update_process_title";"on" "vacuum_cost_delay";"0" "vacuum_cost_limit";"200" "vacuum_cost_page_dirty";"20" "vacuum_cost_page_hit";"1" "vacuum_cost_page_miss";"10" "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_block_size";"8192" "wal_buffers";"16MB" "wal_keep_segments";"0" "wal_level";"archive" "wal_receiver_status_interval";"10s" "wal_receiver_timeout";"1min" "wal_segment_size";"16MB" "wal_sender_timeout";"1min" "wal_sync_method";"fdatasync" "wal_writer_delay";"200ms" "work_mem";"1MB" "xmlbinary";"base64" "xmloption";"content" "zero_damaged_pages";"off" -----Original Message----- From: Josh Berkus [mailto:josh@agliodbs.com] Sent: Thursday, August 07, 2014 7:14 PM To: Tom Lane; James Cloos Cc: pgsql-hackers@postgresql.org; Shaun Thomas; Mark Kirkwood; Ramirez, Danilo Subject: Re: [HACKERS] PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong? On 08/07/2014 04:48 PM, Tom Lane wrote: > plpgsql is not efficient at all about coercions performed as a side > effect of assignments; if memory serves, it always handles them by > converting to text and back. So basically the added cost here came > from float8out() and float4in(). There has been some talk of trying > to do such coercions via SQL casts, but nothing's been done for fear > of compatibility problems. Yeah, that's a weeks-long project for someone. And would require a lot of tests ... -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
<p dir="ltr">Hi,<p dir="ltr">Le 9 août 2014 05:57, "Ramirez, Danilo" <<a href="mailto:Danilo.Ramirez@hmhco.com">Danilo.Ramirez@hmhco.com</a>>a écrit :<br /> ><br /> > Thanks to all forthe great info. We are new to postgresql and this discussion has both instructed us and increased our respect for thedatabase and the community.<br /> ><br /> > I am seeing a behavior that I don’t understand and hopefully you guyscan clear it up.<br /> ><br /> > I am using AWS postgresql db.m3.2xlarge and using pgadmin III 1.18 comparing againstAWS oracle on db.m3.2xlarge using sql developer and TOAD.<br /> ><br /> > I am running a query with 30 tablesin the from clause, getting 137 columns back (this is our most basic query, they get a lot more more complex). Itreturns back 4800 rows.<br /> ><br /> > In oracle 1st run takes 3.92 seconds, 2nd .38 seconds. Scrolling to endtakes and extra 1.5 seconds for total of 5.5.<br /> ><br /> > Using pgadmin, I run the query. Looking at the lowerright hand I can see the time going up. It stops at 8200 ms or close to it every time, then it takes an extra 6 secondsbefore it displays the rows on the screen. 2nd, 3rd, etc. runs all take about same amount of time 8 sec plus 6 sec<br/> ><br /> > I then changed it to return only 1 column back. In oracle/sqldeveloper identical behavior as before,same time. In postgresql it now goes down to 1.8 seconds for 1st, 2nd, etc. runs.<br /> ><br /> > I then changeit so that I am asking for the sum of 1 column. In oracle time goes down to .2 seconds and postgresql now goes downto .2 seconds also.<br /> ><br /> > I then change it back to get the full result set and behavior goes back tooriginal, oracle .38 since its cached, postgresql 8 seconds.<br /> ><p dir="ltr">Are you sure this is postgresql 8 seconds?I'd believe this is more something like postgresql something really low and PgAdmin around 8 seconds displaying it.What I mean is, PgAdmin uses really slow UI components and the time it shows is the time to execute the query and displaythe data. IOW, you shouldn't use it to benchmark. You should better use psql. Or, much better, you should set log_min_duration_statementto 0 and see exactly how much time postgresql needs to execute it.
Hi,
Le 9 août 2014 05:57, "Ramirez, Danilo" <Danilo.Ramirez@hmhco.com> a écrit :
>
> Thanks to all for the great info. We are new to postgresql and this discussion has both instructed us and increased our respect for the database and the community.
>
> I am seeing a behavior that I don’t understand and hopefully you guys can clear it up.
>
> I am using AWS postgresql db.m3.2xlarge and using pgadmin III 1.18 comparing against AWS oracle on db.m3.2xlarge using sql developer and TOAD.
>
> I am running a query with 30 tables in the from clause, getting 137 columns back (this is our most basic query, they get a lot more more complex). It returns back 4800 rows.
>
> In oracle 1st run takes 3.92 seconds, 2nd .38 seconds. Scrolling to end takes and extra 1.5 seconds for total of 5.5.
>
> Using pgadmin, I run the query. Looking at the lower right hand I can see the time going up. It stops at 8200 ms or close to it every time, then it takes an extra 6 seconds before it displays the rows on the screen. 2nd, 3rd, etc. runs all take about same amount of time 8 sec plus 6 sec
>
> I then changed it to return only 1 column back. In oracle/sqldeveloper identical behavior as before, same time. In postgresql it now goes down to 1.8 seconds for 1st, 2nd, etc. runs.
>
> I then change it so that I am asking for the sum of 1 column. In oracle time goes down to .2 seconds and postgresql now goes down to .2 seconds also.
>
> I then change it back to get the full result set and behavior goes back to original, oracle .38 since its cached, postgresql 8 seconds.
>Are you sure this is postgresql 8 seconds? I'd believe this is more something like postgresql something really low and PgAdmin around 8 seconds displaying it. What I mean is, PgAdmin uses really slow UI components and the time it shows is the time to execute the query and display the data. IOW, you shouldn't use it to benchmark. You should better use psql. Or, much better, you should set log_min_duration_statement to 0 and see exactly how much time postgresql needs to execute it.
Pavel