I want to get help with Postgresql.
I investigated that Postgresql could be rather fast in an environment using a secondary index. but It came up with different results on benckmark.
The database I compared was mariadb, and the benchmark tool was sysbench 1.0.8 with the postgresql driver.
Server environment: vmware, Ubuntu 17.04, processor: 4, RAM: 4 GB, Harddisk: 40 GB, Mariadb (v10.3), PostgreSQL (v9.6.4)
The created sysbench progress statement is as follows.
Sysbench /usr/share/sysbench/oltp_read_only.lua --db-driver = mysql --mysql-host = 127.0.0.1 --mysql-port = 3306 --mysql-user = root --mysql-password = ajdajddl75 - Mysql-db = sysbench --tables = 3 --table_size = 100000 --report-interval = 10 --secondary = on --time = 60
Used options
Select only, Num of threads = 1, num of tables = 3, table-size = 100000 and Table-size = 1000000, secondary index select instead of primary key.
My hypothesis was that selecting by secondary index in postgresql is faster than in Mariadb. However, the results depend on table size.
Postgresql was faster than Mariadb when the table size was 1000000, but slower at 100000.
Cluster secondary indexes were faster than those without cluster indexes in pg, but slower than mariadb.
I'd like to see the difference in architecture rather than optimization, so every benchmark executed with default options except for clustered index.
I wonder if there are any settings I missed.
I would be very pleased if someone could explain why these results came up.