Skip site navigation (1) Skip section navigation (2)

Re: Very strange postgresql behaviour

From: Arnau <arnaulist(at)andromeiberica(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Very strange postgresql behaviour
Date: 2007-01-29 14:49:07
Message-ID: 45BE0963.2090205@andromeiberica.com (view raw or flat)
Thread:
Lists: pgsql-admin
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 <arnaulist(at)andromeiberica(dot)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" 
> <http://autos.yahoo.com/green_center/;_ylc=X3oDMTE4MGw4Z2hlBF9TAzk3MTA3MDc2BHNlYwNtYWlsdGFncwRzbGsDZ3JlZW5jZW50ZXI-> 
> at Yahoo! Autos' Green Center.


-- 
Arnau

In response to

pgsql-admin by date

Next:From: Robert D OdenDate: 2007-01-29 15:03:13
Subject: Terminating a query that appears to be hung
Previous:From: Andrew EdsonDate: 2007-01-29 14:25:59
Subject: Re: Very strange postgresql behaviour

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group