Hi, Jim, Jim Nasby wrote: > Adding -performance back in > I would like to try it. > > However in an other post I added that contrary to what I stated > initially all the paramXX columns are not mandatory in the query. So > it seems that requirement make the problem more complexe. Okay, this rules out my functional index over 19 columns. > Doesn't this new requirement rule out this solution? > > No, just group the columns logically. Yes, that's the solution. If you have common groups of columns that appear and disappear synchroneously, pack those together in an (possibly partitioned and/or functional) index. Then rely on the query planner that the combines the appropriate indices via index bitmap scan. > By the way I have test to index each column individually and check > what happens in relation to bitscan map. My test table is 1 > million rows. The explain analyze command shows that a bit scan is > sometimes used but I still end up with queries that can take up to > 10s which is way to much. Is it on the first query, or on repeated queries? It might be that you're I/O bound, and the backend has to fetch indices and rows from Disk into RAM. I currently don't know whether the order of indices in a multi-index bitmap scan is relevant, but I could imagine that it may be useful to have the most selective index scanned first. And keep in mind that, assuming an equal distribution of your parameters, every index bitmap hits 1/10th of the whole table on average, so the selectivity generally is low. The selectivity of a partitioned 3-column index will be much better (about 1/10000th of the whole table), and less index scans and bitmaps have to be generated. A functional index may also make sense to CLUSTER the table to optimize the locality of search results (and so reducing disk I/O). In case your table has low write activity, but high read-only activity, the overhead that comes with the additional index is neglible compared to the performance improvement proper CLUSTERing can generate. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
