Thread: Hints to query parser about indexes

Hints to query parser about indexes

From
"Rigmor Ukuhe"
Date:
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