Skip site navigation (1) Skip section navigation (2)

Re: Using Table Indexes After Joins

From: Willy-Bas Loos <willybas(at)gmail(dot)com>
To: Alex Koay <alexkoay88(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Using Table Indexes After Joins
Date: 2012-04-25 11:30:18
Message-ID: CAHnozTi-0BE97nMLQ8k_poe2d_XaB158RcDvNQFopoTyZa26hg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Guillaume HenriotDate: 2012-04-25 13:01:22
Subject: Re: Returning generated id after a transaction.
Previous:From: Bartosz DmytrakDate: 2012-04-24 18:46:52
Subject: Re: Returning generated id after a transaction.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group