On Fri, 19 Sep 2008, Tom Lane wrote:
Your example shows the IN-list as being sorted, but I wonder whether you actually are sorting the items in practice? If not, you might try that to improve locality of access to the index.
Well, like I said, we generally don't have the luxury of dictating the order of entries in the data source. However, the IN list itself is sorted - more to do with making the logs readable and the tests reproducable than for performance.
However, I have been looking at changing the order of the input data. This particular data source is a 29GB xml file, and I wrote a quick program which sorts that by one key in 40 minutes, which will hopefully allow later data sources (which are easier to sort) to take advantage of spacial locality in the table. However, that key is not the same one as the one used in the query above, hence why I say we can't really dictate the order of the entries. There's another complication which I won't go into.
Also, parsing/planning time could be part of your problem here with 1000 things to look at. Can you adjust your client code to use a prepared query? I'd try SELECT * FROM table WHERE field = ANY($1::text[]) (or whatever the field datatype actually is) and then push the list over as a single parameter value using array syntax. You might find that it scales to much larger IN-lists that way.
Yes, that is a useful suggestion. However, I am fairly clear that the system is disk seek-bound at the moment, so it probably wouldn't make a massive improvement. It would also unfortunately require changing a lot of our code. Worth doing at some point.
Matthew -- "Interwoven alignment preambles are not allowed." If you have been so devious as to get this message, you will understand it, and you deserve no sympathy. -- Knuth, in the TeXbook
