On 7-1-2010 13:38 Lefteris wrote:
I decided to run the benchmark over postgres to get some more experience and insights. Unfortunately, the query times I got from postgres were not the expected ones:
Why were they not expected? In the given scenario, column databases are having a huge advantage. Especially the given simple example is the type of query a column database *should* excel.
You should, at the very least, compare the queries to MyISAM: http://www.mysqlperformanceblog.com/2009/11/05/air-traffic-queries-in-myisam-and-tokutek-tokudb/ But unfortunately, that one also beats your postgresql-results.
The hardware characteristics are: Platform Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz with 8GB RAM and ample disk space (2x 500 GB SATA disk @ 7200 RPM as SW-RAID-0)
Unfortunately, the blogpost fails to mention the disk-subsystem. So it may well be much faster than yours, although its not a new, big or fast server, so unless it has external storage, it shouldn't be too different for sequential scans.
SELECT "DayOfWeek", count(*) AS c FROM ontime WHERE "Year" BETWEEN 2000 AND 2008 GROUP BY "DayOfWeek" ORDER BY c DESC; Reported query times are (in sec): MonetDB 7.9s InfoBright 12.13s LucidDB 54.8s For pg-8.4.2 I got with 3 consecutive runs on the server: 5m52.384s 5m55.885s 5m54.309s
Maybe an index of the type 'year, dayofweek' will help for this query. But it'll have to scan about half the table any way, so a seq scan isn't a bad idea. In this case, a partitioned table with partitions per year and constraint exclusion enabled would help a bit more.
Best regards, Arjen -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
