From: | "Rigmor Ukuhe" <rigmor(dot)ukuhe(at)finestmedia(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Hints to query parser about indexes |
Date: | 2003-06-11 08:19:39 |
Message-ID: | OEEHLFAIJHHMABJPIANIKENCCDAA.rigmor.ukuhe@finestmedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Aarni | 2003-06-11 09:12:15 | zero-length string error ? |
Previous Message | Joanne Formoso | 2003-06-11 07:11:58 | Inheritance question |