Re: Using Table Indexes After Joins

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?

hth

WBL

On Fri, Mar 30, 2012 at 9:31 PM, Alex Koay <alexkoay88(at)gmail(dot)com> wrote:

> 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?
>
> Regards,
> Alex
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth