Mike Mascari wrote:
>
> I realize that this is somewhat off topic, but I
> have seen far better improvments in query speeds
> when using a multifield index instead of individual
> ones.
I only saw an infinitesimal improvement in my case (I guess it
already was as good as it could be), but I appreciate pointing this
option out to me. I believe it can account for better performance in
some cases, but it's already useful in keeping the number of indices
low and it also makes the queries shorter and more readable.
> Perhaps the distribution of your data is more
> "normal" using multikey indexes (I hope). Note
> that if you have a multifield index, and you still
> submit your original query, the planner/optimizer
> doesn't appear smart enough to use the multikey
> index except for the first join condition,
It does that now:
explain SELECT km.km, su.su FROM km, su WHERE (km.id,km.rel,km.item) = (su.id,su.rel,su.item) AND su ~ '^ethanol'
NOTICE: QUERY PLAN:
Nested Loop (cost=5071.34 rows=5 width=76)
-> Seq Scan on su (cost=5039.20 rows=16 width=38)
-> Index Scan using k_km on km (cost=2.01 rows=31376 width=38)
explain SELECT km.km, su.su FROM km, su WHERE km.id = su.id AND km.rel = su.rel AND km.item = su.item AND su ~
'^ethanol';"
NOTICE: QUERY PLAN:
Nested Loop (cost=5071.34 rows=5 width=76)
-> Seq Scan on su (cost=5039.20 rows=16 width=38)
-> Index Scan using k_km on km (cost=2.01 rows=31376 width=38)
and it uses only one individual index when the composite index is dropped:
explain SELECT km.km, su.su FROM km, su WHERE km.id = su.id AND km.rel = su.rel AND km.item = su.item AND su ~
'^ethanol';"
NOTICE: QUERY PLAN:
Nested Loop (cost=5075.54 rows=5 width=76)
-> Seq Scan on su (cost=5039.20 rows=16 width=38)
-> Index Scan using kmidix on km (cost=2.27 rows=31376 width=38)
explain SELECT km.km, su.su FROM km, su WHERE (km.id,km.rel,km.item) = (su.id,su.rel,su.item) AND su ~ '^ethanol'"
NOTICE: QUERY PLAN:
Nested Loop (cost=5075.54 rows=5 width=76)
-> Seq Scan on su (cost=5039.20 rows=16 width=38)
-> Index Scan using kmidix on km (cost=2.27 rows=31376 width=38)
Note that (1) it does not seem to treat the AND clauses and list
comparisons differently (and that's great!), and (2), in this case,
the composite index is just as good as one of the individual indices
-- the one with the largest number of bins (km.id and su.id are unique
for each record, while "rel" and "item" aren't)
> On another note, looking through the backend
> sources reveals that, at one point, partial indices
> were once allowed:
>
> CREATE INDEX k_km on km(item) WHERE item <> 1;
>
> but the grammer has been removed and is now
> illegal (although the code to handle it still
> exists). Perhaps someday this feature will be
> restored.
I like this idea, too.
Thanks a lot,
--Gene