From: | Jan-Ivar Mellingen <jan-ivar(dot)mellingen(at)alreg(dot)no> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Huge speed penalty using <>TRUE instead of =FALSE |
Date: | 2009-07-17 08:12:41 |
Message-ID: | 4A603279.3030205@alreg.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
One of our customers discovered that by replacing <>TRUE with =FALSE in
a query of a table containing 750.000 records reduced the query time
from about 12 seconds to about 60 milliseconds!
The problematic query looks like this:
SELECT * FROM AlarmLogg WHERE Logg_avsluttet <> TRUE AND
Alarm_status='X' ORDER BY ID DESC
If it is changed to this it works as expected:
SELECT * FROM AlarmLogg WHERE Logg_avsluttet = FALSE AND
Alarm_status='X' ORDER BY ID DESC
After investigation (on a smaller dataset on my own database) I found
that the query was resulting in a sequential scan:
"explain analyze SELECT * FROM AlarmLogg WHERE Logg_avsluttet <> TRUE
AND Alarm_status='X' ORDER BY ID DESC"
"Sort (cost=49936.96..49936.96 rows=1 width=405) (actual
time=837.793..837.793 rows=0 loops=1)"
" Sort Key: id"
" Sort Method: quicksort Memory: 17kB"
" -> Seq Scan on alarmlogg (cost=0.00..49936.95 rows=1 width=405)
(actual time=837.782..837.782 rows=0 loops=1)"
" Filter: ((logg_avsluttet <> true) AND ((alarm_status)::text =
'X'::text))"
"Total runtime: 837.896 ms"
The modified query gave this result:
"explain analyze SELECT * FROM AlarmLogg WHERE Logg_avsluttet = FALSE
AND Alarm_status='X' ORDER BY ID DESC"
"Sort (cost=8.36..8.37 rows=1 width=405) (actual time=0.032..0.032
rows=0 loops=1)"
" Sort Key: id"
" Sort Method: quicksort Memory: 17kB"
" -> Index Scan using i_alarmlogg_logg_avsluttet on alarmlogg
(cost=0.00..8.35 rows=1 width=405) (actual time=0.024..0.024 rows=0
loops=1)"
" Index Cond: (logg_avsluttet = false)"
" Filter: ((NOT logg_avsluttet) AND ((alarm_status)::text =
'X'::text))"
"Total runtime: 0.123 ms"
This is a dramatical difference, but I cannot understand why. In my head
"<>TRUE" should behave exactly the same as "=FALSE". This looks like a
bug to me, or am I overlooking something?
This was verified on PostgreSQL 8.3.7, both on Windows Xp and Ubuntu 8.10.
Some relevant details from the table definition:
CREATE TABLE alarmlogg
(
id serial NOT NULL,
alarm_status character varying(1) DEFAULT ''::character varying,
logg_avsluttet boolean DEFAULT false,
...
CONSTRAINT alarmlogg_pkey PRIMARY KEY (id)
)
CREATE INDEX i_alarmlogg_alarm_status
ON alarmlogg
USING btree
(alarm_status);
CREATE INDEX i_alarmlogg_logg_avsluttet
ON alarmlogg
USING btree
(logg_avsluttet);
Regards,
Jan-Ivar Mellingen
Securinet AS
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2009-07-17 08:56:51 | Re: BUG #4926: too few pathkeys for mergeclauses |
Previous Message | Heikki Linnakangas | 2009-07-17 07:50:13 | Re: BUG #4926: too few pathkeys for mergeclauses |