Re: optimisation for a table with frequently used query
От | PFC |
---|---|
Тема | Re: optimisation for a table with frequently used query |
Дата | |
Msg-id | op.ts3uxlzccigqcu@apollo13 обсуждение исходный текст |
Ответ на | Re: optimisation for a table with frequently used query (danmcb <danielmcbrearty@gmail.com>) |
Список | pgsql-general |
> again thanks - bit of a noob question I know, but it's good to > learn :-) Well not really since the answer is quite subtle... You kave two columns A and B. Say you have index on A, and index on B. These queries will make direct use of the index : A=... or any range on A (BETWEEN <, >, <=, >= etc ) B=... or any range on B (BETWEEN <, >, <=, >= etc ) Now if you ask for (A=... AND B=...) or ask for (A=... OR B=...) one index cannot be used, so postgres uses a bitmap scan to combine the indexes (read the docs). It is slightly slower than a direct index scan, but still much faster than not using indexes at all. If you had an index on A,B it would have been used directly. If one of the two indexes has very poor selectivity (like just a few different values), bitmap scan will not be optimal. If your indexes have lots of different values, it will be about as fast as a real index. An index on A,B can also do WHERE A=... ORDER BY A,B without actually doing the sort (it will pick the rows in index order), which is nice for many things, like getting blog comments in order.
В списке pgsql-general по дате отправления: