not using an index is not always a bad choice. that's because the cost of using an index is higher *per record* than the cost of using a sequential scan. since you are requesting all the data, it would be more expensive (take more time) to use the index.
you get sorted output from an index scan, but it seems from your story that the query planner calculated that it would be faster to sort in memory than to fetch each record from disk separately using the index.
That being said, if you can eliminate a join, the query is faster, of course. Probably you have other considerations to split the data in 2 tables?
Is it possible to use the original table indexes after a join?
I have a query like this: SELECT lag(bar.d, 1) OVER (foo.a, foo.b, foo.c) FROM foo NATURAL JOIN bar
with an index on foo(a,b,c), but it doesn't seem to use the index for the sort pre-window. Note that foo and bar have a strict one-to-one relationship.
In such a case, would it be more advisable to use one single table instead?
