Re: Create index on the year of a date column
От | Oleg Bartunov |
---|---|
Тема | Re: Create index on the year of a date column |
Дата | |
Msg-id | Pine.GSO.4.56.0306060951130.1962@ra.sai.msu.su обсуждение исходный текст |
Ответ на | Create index on the year of a date column ("Nick Barr" <nick.barr@webbased.co.uk>) |
Список | pgsql-general |
What if you create sql-function without arguments and use it to create functional index ? Oleg On Thu, 5 Jun 2003, Nick Barr wrote: > Hi, > > I am trying to create an index on the year of a date field, to speed up > some queries. Table structure is as follows > > ------------------------------------------------------------------------ > ---- > CREATE SEQUENCE "sm_item_id_seq" start 1 increment 1 maxvalue > 9223372036854775807 minvalue 1 cache 1; > CREATE TABLE "sm_item" ( > "item_id" int4 DEFAULT nextval('"sm_item_id_seq"'::text) NOT > NULL, > "item_created_date_start" date, > CONSTRAINT "sm_item_pkey" PRIMARY KEY ("item_id") > ) WITHOUT OIDS; > ------------------------------------------------------------------------ > ---- > > And I have tried the following to create the actual index > > ------------------------------------------------------------------------ > ---- > create index sm_item_cdates_idx ON sm_item (extract(year from > item_created_date_start)); > ------------------------------------------------------------------------ > ---- > > The response I get from psql is > > ------------------------------------------------------------------------ > ---- > sm_live=# create index sm_item_cdates_idx ON sm_item (extract(year from > item_created_date_start)); > ERROR: parser: parse error at or near "(" at character 52 > ------------------------------------------------------------------------ > ---- > > This relates to the brackets surrounding the "year from > item_created_date_start" bit. > > Am I doing anything blatantly wrong? Can I actually use the extract > function for an index? Would I still get a speed improvement if I were > to just index the whole of the field, rather than just the year? > > An example query that I have been running is: > > ------------------------------------------------------------------------ > ----SELECT item_id, item_created_date_start FROM sm_item WHERE > extract(year FROM item_created_date_start) = 1685; > ------------------------------------------------------------------------ > ---- > > Which of course has been using a seq scan, as there is absolutely no > index on this column as yet. > > Kind Regards, > > Nick Barr > WebBased Ltd. > > This email and any attachments are confidential to the intended > recipient and may also be privileged. If you are not the intended > recipient please delete it from your system and notify the sender. You > should not copy it or use it for any purpose nor disclose or distribute > its contents to any other person. > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
В списке pgsql-general по дате отправления: