On Fri, Oct 3, 2014 at 1:28 AM, Andrus <kobruleht2@hot.ee> wrote:
> 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.
Also,
*) quit using char() type. use varchar() -- at least in postgres,
it's better in every respect. the char type pads out the fields on
disk. (this is a common noobie error in postgres since that may not
necessarily be true in other databases)
*) numeric type gives fixed point operations and clean comparisons and
so is generally necessary, but it in some cases floating point
(float4/float8) are more compact and give better performance without
much downside.
*) 9.0 is up to 9.0.18. Time to upgrade. (it's a binary only replacement).
*) indexes like this: (kuupaev), (kuupaev, kellaaeg) are mostly
redundant because the composite index can service queries on kuupaev
nearly as well as the single field index. Either kill the single
field index to get better memory utilization or reverse the fields in
the composite index to (kellaaeg, kuupaev) if you make frequent
searches on 'kellaaeg'.
Indexes match quals in left to right order to give the best
performance. So, an index on a,b,c gives good performance for
searches on (a), (a,b), and (a,b,c). There are certain limited
exceptions to this rule but it's a good design principle to know.
merlin
