> 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. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
