BIGINT indexes still with problems

Поиск
Список
Период
Сортировка
От Dan Ruthers
Тема BIGINT indexes still with problems
Дата
Msg-id 20040809031829.93971E5BC7@ws7-2.us4.outblaze.com
обсуждение исходный текст
Ответы Re: BIGINT indexes still with problems  (Gaetano Mendola <mendola@bigfoot.com>)
Re: BIGINT indexes still with problems  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Re: BIGINT indexes still with problems  (Peter Eisentraut <peter_e@gmx.net>)
Список pgsql-general
Hi, I have read in this list and elsewhere the problem with indexes and big int.
Still, I have an index that is used or not, depending of the parameter value used in the query.
I am using PostgreSQL 7.4.3 on Linux RH ES3.

Here's the table:
test=> \d dmaildatum
                Table "public.dmaildatum"
     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 id             | bigint                      | not null
 type           | integer                     | not null
 idowneruser    | bigint                      | not null
 idparent       | bigint                      | not null
 creationdate   | timestamp without time zone | not null
 name           | character varying(1024)     | not null
 deletedate     | timestamp without time zone | not null
 lastmodifydate | timestamp without time zone |
 ffrom          | character varying(255)      |
 fto            | text                        |
 fcc            | text                        |
 fbcc           | text                        |
 fsubject       | text                        |
 fdate          | timestamp without time zone |
 fread          | boolean                     |
 nattachments   | integer                     |
 fsize          | bigint                      |
 hashtml        | boolean                     |
 replyto        | text                        |
 pop3id         | character varying(255)      |
 nvattachments  | integer                     |
Indexes:
    "dmaildatum_pkey" primary key, btree (id)
    "ix_dmaildatum_fdate" btree (fdate)
    "ix_dmaildatum_idowneruser" btree (idowneruser)
    "ix_dmaildatum_idparent" btree (idparent)
    "ix_dmaildatum_pop3id" btree (pop3id)
Foreign-key constraints:
    "fk_dmaildatum_dmailfolderdatum" FOREIGN KEY (idparent) REFERENCES dmailfold
erdatum(id) ON DELETE CASCADE

Now, if I run this query (note the int8 cast - also tried with the '' cast to String, same results):
test=> explain select * from dmaildatum where idparent=int8(783219);
                            QUERY PLAN
------------------------------------------------------------------
 Seq Scan on dmaildatum  (cost=0.00..2241.71 rows=2229 width=272)
   Filter: (idparent = 783219::bigint)
(2 rows)

The index is not used. But with an identical query, only different parameter value:
desknow=> explain select * from dmaildatum where idparent=int8(1187838);
                                          QUERY PLAN

--------------------------------------------------------------------------------
---------------
 Index Scan using ix_dmaildatum_idparent on dmaildatum  (cost=0.00..284.05 rows=
102 width=272)
   Index Cond: (idparent = 1187838::bigint)
(2 rows)

The index is used!
I also did a vacuum analyze, and restarted Postgres and it did not make any difference.
I tried many other ID values (ex 783218 and 783220), and they seem to use the index correctly. Only that value doesn't.

Can anyone explain why Postgres behaves differently in these two cases, or at least point to some hints?

Thanks in advance for all your help

--
_______________________________________________
Find what you are looking for with the Lycos Yellow Pages
http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10


В списке pgsql-general по дате отправления:

Предыдущее
От: Gaetano Mendola
Дата:
Сообщение: Re: Copy data from one table to another, where some records might
Следующее
От: sql@hayes.ch (Simon Hayes)
Дата:
Сообщение: Re: How Two JOINS from one table