It might be that your column may be NULL as well as TRUE or FALSE. I am no expert in this matter though.
/M On Fri, Jul 17, 2009 at 10:12 AM, Jan-Ivar Mellingen<jan-ivar.mellin...@alreg.no> wrote: > 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 > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
