Re: [HACKERS] PostgreSQL vs oracle doing 1 million sqrts am I doing it wrong?
Hi 2014-08-09 10:20 GMT+02:00 Guillaume Lelarge guilla...@lelarge.info: Hi, Le 9 août 2014 05:57, Ramirez, Danilo danilo.rami...@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. yes, try to eliminate a impact of PgAdmin for this purpose use psql \timing \o /dev/null SELECT ... -- your query Regards Pavel p.s. you can send a plans of slow and fast variants.
Re: [HACKERS] PostgreSQL vs oracle doing 1 million sqrts am I doing it wrong?
Hi, Le 9 août 2014 05:57, Ramirez, Danilo danilo.rami...@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.
Re: [HACKERS] 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 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. 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;2 autovacuum_max_workers;3 autovacuum_multixact_freeze_max_age;4 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
