Re: Huge speed penalty using <>TRUE instead of =FALSE

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.mellingen@alreg.no> wrote:
> One of our customers discovered that by replacing <>TRUE with =3DFALSE 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=3D'X' ORDER BY ID DESC
>
> If it is changed to this it works as expected:
> SELECT * FROM AlarmLogg WHERE Logg_avsluttet =3D FALSE AND
> Alarm_status=3D'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=3D'X' ORDER BY ID DESC"
> "Sort =A0(cost=3D49936.96..49936.96 rows=3D1 width=3D405) (actual
> time=3D837.793..837.793 rows=3D0 loops=3D1)"
> " =A0Sort Key: id"
> " =A0Sort Method: =A0quicksort =A0Memory: 17kB"
> " =A0-> =A0Seq Scan on alarmlogg =A0(cost=3D0.00..49936.95 rows=3D1 width=
=3D405)
> (actual time=3D837.782..837.782 rows=3D0 loops=3D1)"
> " =A0 =A0 =A0 =A0Filter: ((logg_avsluttet <> true) AND ((alarm_status)::t=
ext =3D
> 'X'::text))"
> "Total runtime: 837.896 ms"
>
> The modified query gave this result:
> "explain analyze SELECT * FROM AlarmLogg WHERE Logg_avsluttet =3D FALSE
> AND Alarm_status=3D'X' ORDER BY ID DESC"
> "Sort =A0(cost=3D8.36..8.37 rows=3D1 width=3D405) (actual time=3D0.032..0=
.032
> rows=3D0 loops=3D1)"
> " =A0Sort Key: id"
> " =A0Sort Method: =A0quicksort =A0Memory: 17kB"
> " =A0-> =A0Index Scan using i_alarmlogg_logg_avsluttet on alarmlogg
> (cost=3D0.00..8.35 rows=3D1 width=3D405) (actual time=3D0.024..0.024 rows=
=3D0
> loops=3D1)"
> " =A0 =A0 =A0 =A0Index Cond: (logg_avsluttet =3D false)"
> " =A0 =A0 =A0 =A0Filter: ((NOT logg_avsluttet) AND ((alarm_status)::text =
=3D
> '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 "=3DFALSE". 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
> (
> =A0 id serial NOT NULL,
> =A0 alarm_status character varying(1) DEFAULT ''::character varying,
> =A0 logg_avsluttet boolean DEFAULT false,
> =A0 ...
> =A0 CONSTRAINT alarmlogg_pkey PRIMARY KEY (id)
> )
>
> CREATE INDEX i_alarmlogg_alarm_status
> =A0ON alarmlogg
> =A0USING btree
> =A0(alarm_status);
>
> CREATE INDEX i_alarmlogg_logg_avsluttet
> =A0ON alarmlogg
> =A0USING btree
> =A0(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
>