Hi Mike, after mulling over the feedback, I failed to explain my yellow rubber duck why I can't introduce some new tables here to avoid the pathologically long OR statements that match main records against fields.
Basically I had to check if a main record was under a category or any of it's subcategories. This meant first creating a list of all categories, then a very, very long select statement to match against all those categories. The customer has really gone to town on creating an enormous number of categories whereas the system was originally designed to have a few dozen categories and hence performance broke down. The solution was to create an extra table where each category has a map from itself to all it's children. This way I can immediately tell if a main record is under a category or any of the subcategories with a join statement. Here are the before/after numbers for PostgreSQL. As you can see the pathologically slow Derby statement(2561ms) has been fixed. It's also interesting to note that these optimizations do have no effect on PostgreSQL(in the noise). If I'm able to get this improvement to production quality(there are a few pesky issues in the app), then I avoid the performance implosion, so even if PostgreSQL is >5x faster, there are will then be bigger fish to fry in the app and we avoid the deployment issues with PostgreSQL. PostgreSQL/ms Derby/ms Rows Befores After Before After 13 0 0 0 1 0 5 13 0 1 1292 42 46 2561 231 757 44 46 237 223 100 16 13 194 218 121 24 20 157 212 818 46 48 197 244 85 13 11 168 212 41 17 13 135 217 17 10 17 145 223 118 23 25 156 225 21 12 9 151 213 58 17 25 139 212 1 15 13 139 226 1340 62 61 297 220 226 18 36 170 211 7 1 0 0 0 34 4 5 1 1 0 5 4 1 1 0 1 1 0 0 4809 76 69 59 55 91 5 5 1 1 61 10 5 1 1 0 3 6 0 1 0 5 7 0 0 1292 42 48 2566 222 757 45 40 238 217 100 13 17 195 221 121 20 15 175 240 818 40 45 199 217 85 20 11 169 211 41 18 11 135 209 17 10 16 143 211 118 19 16 141 213 21 9 17 153 212 58 22 24 138 217 1 12 11 137 284 1340 48 59 384 227 226 18 16 165 218 7 1 0 1 1 34 7 4 1 1 0 5 3 0 1 0 1 1 0 0 4809 73 68 53 58 91 6 5 1 1 61 6 5 1 2 0 5 4 1 1 13 0 0 1 0 11 0 0 1 0 20031 914 934 9907 6333 -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/