Re: How to find greatest record before known values fast - Mailing list pgsql-general

From Andrus
Subject Re: How to find greatest record before known values fast
Date
Msg-id C2F1B84DC3F14AFD96F3132C78991CEB@dell2
Whole thread Raw
In response to Re: How to find greatest record before known values fast  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
Responses Re: How to find greatest record before known values fast
List pgsql-general
Hi!

>So kellaaeg is a time? Your best bet here would be to create an index that
>is an actual timestamp comprised of both >kuupaev and kellaaeg. You could
>do this with to_timestamp by concatinating both fields together, or it may
>be easier to replace the space in kellaaeg with a colon and cast it to
>time, then add the two:
>   kuupaev + replace( kellaaeg, ' ', ':' )::time
>I know you can't alter the table, but can you create a view on top of the
>table? If you did that, you could have a real >timestamp field in the view
>that is calculated from kuupaev and kellaaeg and you can create a
>functional index that >uses the same calculation. That would be the easiest
>way to use this.

Thank you.
I solved this by creating composite index on 3 columns and re-writing query
as Tom recommended.
It looks like Tom's recommendation is simpler for me.

Andrus.



pgsql-general by date:

Previous
From: Michael Paquier
Date:
Subject: Re: Creating a PL/pgSQL function that returns multiple out parameters and refcursor
Next
From: Cedric Berger
Date:
Subject: Re: Getting my Database name in a C Extension