Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 4246A9FB3AC for ; Mon, 29 Jan 2007 10:49:17 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 73448-09 for ; Mon, 29 Jan 2007 10:49:10 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from fafnir.androme.com (fafnir.androme.com [62.58.96.158]) by postgresql.org (Postfix) with ESMTP id EB0799FA377 for ; Mon, 29 Jan 2007 10:49:09 -0400 (AST) Received: by fafnir.androme.com (Postfix, from userid 1003) id 6421EB92135; Mon, 29 Jan 2007 15:49:08 +0100 (CET) Message-ID: <45BE0963.2090205@andromeiberica.com> Date: Mon, 29 Jan 2007 15:49:07 +0100 From: Arnau Reply-To: arnaulist@andromeiberica.com User-Agent: Thunderbird 1.5.0.9 (Windows/20061207) MIME-Version: 1.0 To: "pgsql-admin@postgresql.org" Subject: Re: Very strange postgresql behaviour References: <39579.51535.qm@web34203.mail.mud.yahoo.com> In-Reply-To: <39579.51535.qm@web34203.mail.mud.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/260 X-Sequence-Number: 24416 Hi Andrew, > 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; The result is the same, in fact, the original query was select customer_app_config_id, customer_app_config_name from customer_app_config where customer_app_config_id in ( 5929, 11527) order by customer_app_config_id; Any other idea? > > I hope that helps. > > > */Arnau /* 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. -- Arnau