Hi!
Below the Perl program number 1. Programs 2 - 20 are similar but CREATE TABLE etc. removed and $j runs from 'program number' - 1 up.
Connections were persistent if I understand the PostgreSQL Perl interface right.
Since there were no updates to rows, the well-known PostgreSQL VACUUM problem should not slow down PostgreSQL.
Below also the postgresql.conf file. fsync=OFF to speed up PostgreSQL, and I increased WAL buffers to 64. The number of shared buffers 2000 should be able give a good hit rate to a table which only has < 400 000 rows and where accesses are heavily concentrated.
Best regards,
Heikki Tuuri
Innobase Oy
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com
..........
use Pg;
$sum = 0;
$conn = Pg::connectdb("dbname=test");
$conn->exec("drop table speed1");
$conn->exec(
"create table speed1 (a int not null, b int, c int)");
$conn->exec(
"create unique index speed1_a on speed1(a)");
$conn->exec(
"create index speed1_b on speed1(b)");
for ($j = 0; $j < 1000000; $j = $j + 50) {
$conn->exec("insert into speed1 values ($j, $j, $j)");
$result = $conn->exec("select * from speed1 where a = $j - 100");
$sum += $result->ntuples;
if ($j % 10000 == 0) {
print "round $j\n";
}
}
print "$sum rows fetched\n";
.............
#
Connection Parameters
#
#tcpip_socket = false
#ssl = false
max_connections = 100
#port = 5432
#hostname_lookup = false
#show_source_port = false
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#virtual_host = ''
#krb_server_keyfile = ''
#
Performance
#
#sort_mem = 512
shared_buffers = 2000
fsync = false
#
Optimizer Parameters
#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true
#ksqo = false
#geqo = true
#effective_cache_size = 1000 # default in 8k pages
#random_page_cost = 4
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025
#geqo_selection_bias = 2.0 # range 1.5-2.0
#
GEQO Optimizer Parameters
#
#geqo_threshold = 11
#geqo_pool_size = 0 #default based in tables, range 128-1024
#geqo_effort = 1
#geqo_generations = 0
#geqo_random_seed = -1 # auto-compute seed
#
Inheritance
#
#sql_inheritance = true
#
Deadlock
#
Deadlock
#
#deadlock_timeout = 1000
#
Expression Depth Limitation
#
#max_expr_depth = 10000 # min 10
#
Write-ahead log (WAL)
#
wal_buffers = 64 # min 4
wal_files = 4 # range 0-64
#wal_sync_method = fsync # fsync or fdatasync or open_sync or open_datasync
Note: default wal_sync_method varies across platforms
#wal_debug = 0 # range 0-16
#commit_delay = 0 # range 0-100000
#commit_siblings = 5 # range 1-1000
#checkpoint_segments = 3 # in logfile segments (16MB each), min 1
#checkpoint_timeout = 300 # in seconds, range 30-3600
#
Debug display
#
#silent_mode = false
#log_connections = false
#log_timestamp = false
#log_pid = false
#debug_level = 0 # range 0-16
#debug_print_query = false
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#ifdef USE_ASSERT_CHECKING
#debug_assertions = true
#endif
#
Syslog
#
#ifdef ENABLE_SYSLOG
#syslog = 0 # range 0-2
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#endif
#
Statistics
#
#show_parser_stats = false
#show_planner_stats = false
#show_executor_stats = false
#show_query_stats = false
#ifdef BTREE_BUILD_STATS
#show_btree_build_stats = false
#endif
#
Lock Tracing
#
#trace_notify = false
#ifdef LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_spinlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0
#endif
