Hi,
I have some troubles with indexing colums, or more like to give query parser
some hints to use index.
I created index:
CREATE INDEX "Text_type_language_status_enddate" ON "TEXT" USING btree
("TYPE", "LANGUAGE", "STATUS", "END_DATE");
And query is:
SELECT "TEXT_ID", "CAPTION" FROM "TEXT" WHERE "TYPE"='FORUM_THEME' AND
"LANGUAGE" ='EST' AND "STATUS"='TEXT_STATUS_ACTIVE' ORDER BY "END_DATE" DESC
LIMIT 5
When i do EXPLAIN
Limit (cost=349.06..349.06 rows=5 width=54)
-> Sort (cost=349.06..349.06 rows=91 width=54)
-> Index Scan using Text_type_language_status_start on TEXT
(cost=0.00..346.11 rows=91 width=54)
the problem is that I dont want this Sort command there.
When i add some more columns into order by clause (they dont change result)
index is used "properly"
Query:
SELECT "TEXT_ID", "CAPTION" FROM "TEXT" WHERE "TYPE"='FORUM_THEME' AND
"LANGUAGE" ='EST' AND "STATUS"='TEXT_STATUS_ACTIVE' ORDER BY "TYPE" DESC,
"LANGUAGE" DESC, "STATUS" DESC, "END_DATE" DESC LIMIT 5
EXPLAIN
Limit (cost=0.00..19.04 rows=5 width=99)
-> Index Scan Backward using Text_type_language_status_endda on TEXT
(cost=0.00..346.11 rows=91 width=99)
Do i have to rewrite all my queries to take advantage of some indexes? There
is quite many queries to rewrite and before i start doing it i would like to
know what is my options.
Rigmor Ukuhe
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.478 / Virus Database: 275 - Release Date: 06.05.2003
