Very strange postgresql behaviour - Mailing list pgsql-admin

Hi all,

   I have postgresql 7.4.2 running on debian and I have the oddest
postgresql behaviour I've ever seen.

I do the following queries:


espsm_asme=# select customer_app_config_id, customer_app_config_name
from customer_app_config where customer_app_config_id = 5929 or
customer_app_config_id = 11527 order by customer_app_config_id;


  customer_app_config_id | customer_app_config_name
------------------------+--------------------------
                    5929 | INFO
(1 row)


   I do the same query but changing the order of the or conditions:


espsm_asme=# select customer_app_config_id, customer_app_config_name
from customer_app_config where customer_app_config_id = 11527 or
customer_app_config_id = 5929 order by customer_app_config_id;


  customer_app_config_id | customer_app_config_name
------------------------+--------------------------
                   11527 | MOVIDOSERENA TONI 5523
(1 row)



   As you can see, the configuration 5929 and 11527 both exists, but
when I do the queries they don't appear.

   Here below you have the execution plans. Those queries use an index,
I have done reindex table customer_app_config but nothing has changed.

espsm_asme=# explain analyze select customer_app_config_id,
customer_app_config_name from customer_app_config where
customer_app_config_id = 11527 or customer_app_config_id = 5929 order by
customer_app_config_id;

                   QUERY PLAN


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=10.28..10.29 rows=2 width=28) (actual time=0.252..0.253
rows=1 loops=1)
    Sort Key: customer_app_config_id
    ->  Index Scan using pk_cag_customer_application_id,
pk_cag_customer_application_id on customer_app_config  (cost=0.00..10.27
rows=2 width=28) (actual time=0.168..0.232 rows=1 loops=1)
          Index Cond: ((customer_app_config_id = 11527::numeric) OR
(customer_app_config_id = 5929::numeric))
  Total runtime: 0.305 ms
(5 rows)

espsm_asme=# explain analyze select customer_app_config_id,
customer_app_config_name from customer_app_config where
customer_app_config_id = 5929 or customer_app_config_id = 11527 order by
customer_app_config_id;

                   QUERY PLAN


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=10.28..10.29 rows=2 width=28) (actual time=0.063..0.064
rows=1 loops=1)
    Sort Key: customer_app_config_id
    ->  Index Scan using pk_cag_customer_application_id,
pk_cag_customer_application_id on customer_app_config  (cost=0.00..10.27
rows=2 width=28) (actual time=0.034..0.053 rows=1 loops=1)
          Index Cond: ((customer_app_config_id = 5929::numeric) OR
(customer_app_config_id = 11527::numeric))
  Total runtime: 0.114 ms
(5 rows)

   The table definition is the following:

espsm_asme=# \d customer_app_config
                   Table "public.customer_app_config"
           Column          |         Type          |     Modifiers
--------------------------+-----------------------+--------------------
  customer_app_config_id   | numeric(10,0)         | not null
  customer_app_config_name | character varying(32) | not null
  keyword                  | character varying(43) |
  application_id           | numeric(10,0)         | not null
  customer_id              | numeric(10,0)         | not null
  customer_app_contents_id | numeric(10,0)         |
  number_access_id         | numeric(10,0)         |
  prefix                   | character varying(10) |
  separator                | numeric(1,0)          | default 0
  on_hold                  | numeric(1,0)          | not null default 0
  with_toss                | numeric(1,0)          | not null default 0
  number_id                | numeric(10,0)         |
  param_separator_id       | numeric(4,0)          | default 1
  memory_timeout           | integer               |
  with_memory              | numeric(1,0)          | default 0
  session_enabled          | numeric(1,0)          | default 0
  session_timeout          | integer               |
  number                   | character varying(15) |
Indexes:
     "pk_cag_customer_application_id" primary key, btree
(customer_app_config_id)
     "un_cag_kwordnumber" unique, btree (keyword, number_id)
     "idx_cappconfig_ccontentsid" btree (customer_app_contents_id)
     "idx_cappconfig_cusidappid" btree (customer_id, application_id)
     "idx_cappconfig_customerid" btree (customer_id)
     "idx_cappconfig_onhold" btree (on_hold)
     "idx_cappconfig_onholdkeyw" btree (on_hold, keyword)
Rules:

   A lot of rules that I don't paste as matter of length.


   Do you have any idea about how I can fix this?

--
Arnau