Re: CPU 0.1% IOWAIT 99% for decisonnal queries
От | Patrick Vedrines |
---|---|
Тема | Re: CPU 0.1% IOWAIT 99% for decisonnal queries |
Дата | |
Msg-id | 007a01c53059$7ca13750$1e0baf96@pcpatrickxp обсуждение исходный текст |
Ответ на | CPU 0.1% IOWAIT 99% for decisonnal queries ("Patrick Vedrines" <patrick.vedrines@adpcl.com>) |
Список | pgsql-performance |
Hello Richard, > Perhaps look into clustering the tables. Good idea : I will try to go further into this way. > > There is no index on the aggregate table since the criterias, their > > number and their scope are freely choosen by the customers. > > Hmm... not convinced this is a good idea. Long days ago, when my application used Informix, I've try to index the aggregate table: It was a nightmare to manage all these indexes (and their volume) for a uncertain benefit. > If you don't have any indexes and the table isn't clustered then PG has > no choice but to scan the entire table for every query. As you note, > that's going to destroy your cache. You can increase the RAM but sooner > or later, you'll get the same problem. I agree with you : You remarks take me not to rely to the cache features. > 3. Split your tables into two - common fields, uncommon fields, that way > filtering on the common fields might take less space. > 4. Split your tables by date, one table per month or year. Then re-write > your customers' queries on-the-fly to select from the right table. Will > only help with queries on date of course. That forces me to rewrite my query generator which is already a very complex program (in fact the heart of the system) > 5. Place each database on its own machine or virtual machine so they > don't interfere with each other. I'm afraid I don't have the money for that. As Simon and Gustavo suggested, I will check my SCSI disks first. Thank a lot for your advises ! Amicalement, Patrick
В списке pgsql-performance по дате отправления: