Hello,
Started to work with big tables (like 300GB) and performance problems started to appear. :(
To simplify things - table has an index on From an index on To columns. And it also have several other not indexed
columns.There are 100000+ of different values for From and the same for To.
I execute simple query "select * from bigtable where From='something'". Query returns like 1000 rows and takes 5++
secondsto complete. As far as I understand the query is slow because:
- first it has to retrieve pointers to rows with data from index. That goes fast.
- retrieve all the rows one by one. There we have 100% random read because rows with the same From is distributed
evenlythrough all the 300GB and most probably nothing is cached. So there we are limited by _one_ disk performance
independentlyof how many disks we have in storage? And in case storage consists of 15k rpm Cheetahs with 3.5ms average
readseek time we should expect not more than ~285 rows per second?
I feel that I'm overlooking something here. But I'm new into data warehousing. :)
Also this query should greatly benefit from parallel execution or async IO. Storage (seeks/second) scales almost
linearlywhen it has a lot of disks. And query is completely IO bound so it should scale well on single server.
And I cannot use some index organized table or table partitioned by From :) because there are at least 2 similar
indexesby which queries can be executed - From and To.
Ideas for improvement? Greenplum or EnterpriseDB? Or I forgot something from PostgreSQL features.
Thanks,
Mindaugas