From: Andrew Edson <cheighlund@yahoo.com>
To: arnaulist@andromeiberica.com, "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Very strange postgresql behaviour
Date: Mon, 29 Jan 2007 06:25:59 -0800 (PST)
Message-ID: <39579.51535.qm@web34203.mail.mud.yahoo.com> (raw)
In-Reply-To: <45BDE670.8010607@andromeiberica.com>
Arnau,
I'm not sure about this for 7.4.2, but I'm running 8.1.3 and when I ran into a problem like that (having to select two distinct options) the solution that worked for me was to put the entire or statement within parentheses. In your case, that would be this:
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;
I hope that helps.
Arnau <arnaulist@andromeiberica.com> wrote:
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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
---------------------------------
Looking for earth-friendly autos?
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: cheighlund@yahoo.com
Subject: Re: Very strange postgresql behaviour
In-Reply-To: <39579.51535.qm@web34203.mail.mud.yahoo.com>
References: <45BDE670.8010607@andromeiberica.com> <39579.51535.qm@web34203.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox