On Tue, Sep 16, 2008 at 12:24 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > On Tue, Sep 9, 2008 at 10:52 AM, regme please <regmeplease@xxxxxxxxx> wrote: >> Hi all. >> I usually create indexes accordingly to the queries used in my software. >> This means the more often than not I have composited indexes over more than >> one column. >> What'd be in PGSQL (v8.3+) the pros and cons of having instead only >> one-column indexes? >> Thanks in advance. > > Pro: > Fewer number of smaller well traveled indexes (more efficient from > cache perspective). > > Con: > Operations that look up multiple fields simultaneously run slower > (sometimes much slower). > > Note that when reviewing sql written by other people in the > performance list, probably the most common optimization suggestion is > to use composite indexes. The other, closely related optimization is functional indexes. If you need to look up stuff based on date_trunc() then create indexes on that. Next I'd say partial indexes.
