Create index on the year of a date column
От | Nick Barr |
---|---|
Тема | Create index on the year of a date column |
Дата | |
Msg-id | 8F4A22E017460A458DB7BBAB65CA6AE502A9E6@webbased9 обсуждение исходный текст |
Ответы |
Re: Create index on the year of a date column
(Bruno Wolff III <bruno@wolff.to>)
Re: Create index on the year of a date column (Ron Johnson <ron.l.johnson@cox.net>) Re: Create index on the year of a date column (Oleg Bartunov <oleg@sai.msu.su>) Re: Create index on the year of a date column (Tom Lane <tgl@sss.pgh.pa.us>) Re: Create index on the year of a date column (Stephan Szabo <sszabo@megazone23.bigpanda.com>) |
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: