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
nullcustomer_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