pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed[OT] Very strange postgresql behaviour
5+ messages / 4 participants
[nested] [flat]
* [OT] Very strange postgresql behaviour
@ 2007-01-29 12:21 Arnau <arnaulist@andromeiberica.com>
0 siblings, 2 replies; 5+ messages in thread
From: Arnau @ 2007-01-29 12:21 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [OT] Very strange postgresql behaviour
@ 2007-01-29 13:27 Bill Moran <wmoran@collaborativefusion.com>
parent: Arnau <arnaulist@andromeiberica.com>
1 sibling, 1 reply; 5+ messages in thread
From: Bill Moran @ 2007-01-29 13:27 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; +Cc: pgsql-performance
In response to Arnau <arnaulist@andromeiberica.com>:
>
> 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.
[snip]
Just a guess, but perhaps your index is damaged. Have you tried
REINDEXing?
--
Bill Moran
Collaborative Fusion Inc.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [OT] Very strange postgresql behaviour
@ 2007-01-29 13:35 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Arnau <arnaulist@andromeiberica.com>
1 sibling, 0 replies; 5+ messages in thread
From: Heikki Linnakangas @ 2007-01-29 13:35 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; +Cc: pgsql-performance
Arnau wrote:
> Hi all,
>
> I have postgresql 7.4.2 running on debian and I have the oddest
> postgresql behaviour I've ever seen.
You should upgrade. The latest 7.2 release is 7.4.15
> I do the following queries:
> ...
At first glance this looks like a bug in PostgreSQL, but..
> Rules:
>
> A lot of rules that I don't paste as matter of length.
Is there any SELECT rules by chance that might explain this?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [OT] Very strange postgresql behaviour
@ 2007-01-29 15:40 Arnau <arnaulist@andromeiberica.com>
parent: Bill Moran <wmoran@collaborativefusion.com>
0 siblings, 1 reply; 5+ messages in thread
From: Arnau @ 2007-01-29 15:40 UTC (permalink / raw)
To: pgsql-performance
Hi Bill,
> In response to Arnau <arnaulist@andromeiberica.com>:
>> 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.
>
> [snip]
>
> Just a guess, but perhaps your index is damaged. Have you tried
> REINDEXing?
>
Yes, I have tried with:
reindex table customer_app_config
reindex index pk_cag_customer_application_id
but nothing changed. I also tried to drop the index:
espsm_asme=# begin; drop index pk_cag_customer_application_id;
BEGIN
ERROR: cannot drop index pk_cag_customer_application_id because
constraint pk_cag_customer_application_id on table customer_app_config
requires it
HINT: You may drop constraint pk_cag_customer_application_id on table
customer_app_config instead.
espsm_asme=# rollback;
ROLLBACK
But I can't remove the constraint as it's the primary key and there
are foreign keys over it
--
Arnau
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [OT] Very strange postgresql behaviour
@ 2007-01-29 19:47 Carlos Moreno <moreno_pg@mochima.com>
parent: Arnau <arnaulist@andromeiberica.com>
0 siblings, 0 replies; 5+ messages in thread
From: Carlos Moreno @ 2007-01-29 19:47 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; +Cc: pgsql-performance
Arnau wrote:
> Hi Bill,
>
>> In response to Arnau <arnaulist@andromeiberica.com>:
>>
>>> 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;
>>
Just wild guessing: is there any chance that there may be some form of
"implicit" limit modifier for the select statements on this table? Does
the
behaviour change if you add "limit 2" at the end of the query? Does it
change if you use customer_app_config_id in (5929, 11527) instead?
Another wild guess: if the data is somewhat corrupt, maybe a vacuum
analyze would detect it? Or perhaps try pg_dumping, to see if pg_dump
at some point complains about mssing or corrupt data?
HTH,
Carlos
--
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-01-29 19:47 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-29 12:21 [OT] Very strange postgresql behaviour Arnau <arnaulist@andromeiberica.com>
2007-01-29 13:27 ` Bill Moran <wmoran@collaborativefusion.com>
2007-01-29 15:40 ` Arnau <arnaulist@andromeiberica.com>
2007-01-29 19:47 ` Carlos Moreno <moreno_pg@mochima.com>
2007-01-29 13:35 ` Heikki Linnakangas <heikki@enterprisedb.com>
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