On Fri, Oct 3, 2014 at 1:28 AM, Andrus <kobruleht2@xxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
