FAQ
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

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 11, '03 at 8:17a
activeJun 11, '03 at 8:17a
posts1
users1
websitepostgresql.org
irc#postgresql

1 user in discussion

Rigmor Ukuhe: 1 post

People

Translate

site design / logo © 2022 Grokbase