Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Header And Logo
|
Site Navigation
[no subject]
Maybe I could, that way, avoid the use of partitioned multi-column indexes. Oscar --------------------------------- Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min. --0-1643493875-1144942806=:44309 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Markus,<br><br>Markus Schaber <schabi(at)logix-tt(dot)com> wrote:<br><br>>Hi, Oscar,<br>><br>>Please reply to the list and not privately, so others can learn from<br>>your replies, and possibly have better Ideas than me.<br><br>That was my intention. I made a mistake.<br><br>>Oscar Picasso wrote:<br>><br>>> I cannot group the columns logically. Any column may or may not appear<br>>> in a query.<br>><br>>That's suboptimal.<br>><br>>> Summrarizing what I have learned:<br>>> - I cannot use multicolumn indexes because I cannot group the column<br>>> logically.<br>>> - I cannot use funtional indexes<br>>> - I cannot use clustering.<br>><br>>You still can have a set of partitioned multi-column indices,<br>>overlapping enough that every combination of columns is covered (or risk<br>>a sequential sub scan for the last two or three columns, this should not<br>>hurt too much if the first 17 columns were selective enough).<br>><br>>The main problem with indices is that they also decrease write performance.<br>><br>>If disk costs are not limited, it will make sense to have WAL, table and<br>>indices on different disks / raid arrays, to parallelize writes.<br>><br>>Btw, I guess you have multiple, concurrent users?<br><br>Yes I do.<br><br>I have just made other tests with only the individual indexes and performance is much better than previously. Obviously there was an I/O problem during my initial test.<br><br>Something interesting though. If I use few columns in the query the results come very quickly and pg does a sequential scan. <br><br>When it reachs some threshold (4 or 5 columns) pg switches to bitmap scans. It then takes an almost constant time (~ 2500 ms) not matter how many more columns I add to the where clause.<br><br>Interestingly enough, queries with many columns are less common. They also return less results and even many times no result at all. <br><br>From the user point of view it would be nice to have a waiting time lower than 2500ms for these queries. Maybe I could achieve that goal simply by tuning postgresql. In a such case where should I look first in order to increase bitmap scanning? <br><br>Maybe I could, that way, avoid the use of partitioned multi-column indexes.<br><br>Oscar<br><br><br><p> <hr size=1>Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. <a href="http://us.rd.yahoo.com/mail_us/taglines/postman7/*http://us.rd.yahoo.com/evt=39666/*http://beta.messenger.yahoo.com";> Great rates starting at 1¢/min. --0-1643493875-1144942806=:44309--
- Prev by Date: [no subject]
- Next by Date: [no subject]
- Previous by thread: [no subject]
- Next by thread: [no subject]
- Indexes: