> I'm not sure what that comment is supposed to mean.
>
> PG is using the index for the condition
> dok.kuupaev BETWEEN '2007-11-01' AND '2007-12-04'
>
> but there is no index that matches the expression
> dok.kuupaev||dok.kellaaeg BETWEEN '2007-11-01' AND '2007-12-0423 59'
>
> If you look at your explain output, you'll see that step is taking a
> lot of time, and it's inside a nested loop, which means it's run
> repeatedly.
Postgres must use index to filter out rows matching to the condition
dok.kuupaev BETWEEN '2007-11-01' AND '2007-12-04'
this returns 121 rows.
Additional condition
dok.kuupaev||dok.kellaaeg BETWEEN '2007-11-01' AND '2007-12-0423 59'
should be evaluated only for these 121 rows.
> Have you run a VACUUM ANALYZE on the tables involved with this query
> recently? It's possible that PG has outdated statistics and is
> running a poor plan as a result.
I have the follwing command in end of postgresql.conf file:
stats_start_collector = on
stats_row_level = on
autovacuum = on
So autovacuum should be running.
> Just add the index and rerun to see if it helps. If it doesn't, then
> drop the index. I have absolutely no way to investigate this for you.
I tried
CREATE INDEX dok_kuupaev_kellaaeg_idx ON dok ((kuupaev||kellaaeg));
but got error
ERROR: functions in index expression must be marked IMMUTABLE
SQL state: 42P17
How to create such index ?
> For example, in your query, you have a text string meant to represent
> a date: '2007-12-0423 59'
>
> This is not a valid date/time, but PostgreSQL has no way to know that
> because it's just a text string. As a result, you're query is liable
> to give you outright incorrect results.
My fields are kuupaev date and kellaaeg char(5)
kellaaeg is in format hh mm
I compare this always with kuupaev||kellaaeg
'2007-12-0423 59' is my valid datetime for to be used for this conversion.
I can probably convert kuupaev||kellaaeg to a datetime and use datetime
comparison instead of this. Will this increase perfomance ?
Andrus.
