<story><title>Index Merges vs. Composite Indexes in Postgres and MySQL</title><url>https://sirupsen.com/index-merges</url></story><parent_chain><item><author>winrid</author><text>There are some DBs where the order of fields doesn&amp;#x27;t matter in the compound index if you&amp;#x27;re just searching by one field (but of course is less efficient). I think Oracle supports this.</text></item><item><author>magicalhippo</author><text>A composite index can also be used for a partial index scan[1], so if you&amp;#x27;re frequently looking for just int100 as well as the int100,int1000 combination (as per the article&amp;#x27;s example), then a composite index int100,int1000 can be used for queries just filtering on int100.&lt;p&gt;The order of the columns in the composite index might matter then. We got some nice savings by reordering columns in indexes and changing the join order in queries (our DB wasn&amp;#x27;t &lt;i&gt;that&lt;/i&gt; smart) or adding a &amp;quot;useless&amp;quot; filters to the where clause, allowing us to consolidate multiple indexes into one composite one.&lt;p&gt;[1]: might be using the wrong terminology here, I&amp;#x27;m not talking about a partial index[2], but using only the first N dimensions of a M-dimensional index.&lt;p&gt;[2]: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.postgresql.org&amp;#x2F;docs&amp;#x2F;current&amp;#x2F;indexes-partial.html&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.postgresql.org&amp;#x2F;docs&amp;#x2F;current&amp;#x2F;indexes-partial.html&lt;/a&gt;</text></item></parent_chain><comment><author>dspillett</author><text>All can search an index by just one field if it is the first field covered by the index, few can use an index if your predicate concerns the second and not the first.&lt;p&gt;IIRC Oracle does support this and calls it a skip-scan. If the first column in the index is not very selective this can be very efficient, though if the first column &lt;i&gt;is&lt;/i&gt; good in terms of selectivity then a skip-scan will be very inefficient and the DB might as well just scan the whole index. Given that a good choice of index usually has a fairly selective column as its first, and that unless storage space is very cramped you are likely better off just defining an extra index without the column that you want to not touch in some queries, this means that a skip-scan isn&amp;#x27;t helpful as often as one might think which is one of the reasons DB engine maintainers give for not spending time implementing and maintaining the feature.&lt;p&gt;There are some use cases where having the option to skip-scan is definitely a significant bonus, but they are rare enough that I understand why most engine maintainers have not implemented them.</text></comment>
<story><title>Index Merges vs. Composite Indexes in Postgres and MySQL</title><url>https://sirupsen.com/index-merges</url></story><parent_chain><item><author>winrid</author><text>There are some DBs where the order of fields doesn&amp;#x27;t matter in the compound index if you&amp;#x27;re just searching by one field (but of course is less efficient). I think Oracle supports this.</text></item><item><author>magicalhippo</author><text>A composite index can also be used for a partial index scan[1], so if you&amp;#x27;re frequently looking for just int100 as well as the int100,int1000 combination (as per the article&amp;#x27;s example), then a composite index int100,int1000 can be used for queries just filtering on int100.&lt;p&gt;The order of the columns in the composite index might matter then. We got some nice savings by reordering columns in indexes and changing the join order in queries (our DB wasn&amp;#x27;t &lt;i&gt;that&lt;/i&gt; smart) or adding a &amp;quot;useless&amp;quot; filters to the where clause, allowing us to consolidate multiple indexes into one composite one.&lt;p&gt;[1]: might be using the wrong terminology here, I&amp;#x27;m not talking about a partial index[2], but using only the first N dimensions of a M-dimensional index.&lt;p&gt;[2]: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.postgresql.org&amp;#x2F;docs&amp;#x2F;current&amp;#x2F;indexes-partial.html&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.postgresql.org&amp;#x2F;docs&amp;#x2F;current&amp;#x2F;indexes-partial.html&lt;/a&gt;</text></item></parent_chain><comment><author>mattashii</author><text>This dependz on the index type, but PostgreSQL also supports queries based on arbitrary attributes of multi-attribute indexes: both GIN and BRIN can be used for queries on any of the indexed expressions.&lt;p&gt;Maybe eventually PG will also get index skip scans for btrees, which could allow for arbitrary  columns searches in the btree; but we&amp;#x27;re not there yet by a large margin.</text></comment>