pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIS NULL vs IS NOT NULL
2+ messages / 2 participants
[nested] [flat]
* IS NULL vs IS NOT NULL
@ 2005-02-25 10:10 Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
0 siblings, 1 reply; 2+ messages in thread
From: Vig, Sandor (G/FI-2) @ 2005-02-25 10:10 UTC (permalink / raw)
To: pgsql-performance
Hi,
Another "funny" thing: I have a query which runs
on (Linux) PostgreSQL 7.4.x under 10 sec. I tried
to run it on (Windows) PostgreSQL 8.0 yesterday.
It didn't finished at all! (I shoot it down after 10 minutes)
I made various tests and I figured out something interesting:
The same query with:
A, "history.undo_action_id > 0" runs in 10 sec.
B, "history.undo_action_id is not null" runs in 10 sec.
C, "history.undo_action_id is null" runs forever (?!)
I used EXPLAIN but I couldn't figure out what the problem was.
In every explain output are 3 lines:
" -> Index Scan using speed_3 on history (.........)"
" Index Cond: (type_id = 6)"
" Filter: (undo_action_id IS NOT NULL)"
where "speed_3" is a btree index on history.type_id. There is also an index
for history.undo_action_id (btree) but it is not used.
The tables are well indexed, and have about 200.000 records.
The SQL file and the 3 scenarios are in attachment.
Help, anyone?
Vig Sándor
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from any
computer.
Attachments:
_is_null.txt (text/plain, 1.2K) download
QUERY PLAN
"Nested Loop (cost=1.10..6331.32 rows=1 width=84)"
" -> Nested Loop (cost=1.10..6325.56 rows=1 width=62)"
" -> Nested Loop (cost=1.10..6319.53 rows=1 width=70)"
" Join Filter: ((""inner"".snumber = ""outer"".snumber) AND (""outer"".event_id = ""inner"".event_id))"
" -> Index Scan using speed_3 on history (cost=0.00..393.33 rows=1 width=24)"
" Index Cond: (type_id = 6)"
" Filter: (undo_action_id IS NULL)"
" -> Hash Join (cost=1.10..5822.46 rows=6916 width=54)"
" Hash Cond: (""outer"".field_id = ""inner"".field_id)"
" -> Seq Scan on seats (cost=0.00..4887.80 rows=172880 width=24)"
" -> Hash (cost=1.08..1.08 rows=8 width=46)"
" -> Seq Scan on fields (cost=0.00..1.08 rows=8 width=46)"
" -> Index Scan using i_action_id2 on actions (cost=0.00..6.02 rows=1 width=8)"
" Index Cond: (""outer"".action_id = actions.action_id)"
" Filter: (((created)::date = '2005-02-24'::date) AND (user_id = 23))"
" -> Index Scan using events_pkey on events (cost=0.00..5.75 rows=1 width=46)"
" Index Cond: (""outer"".event_id = events.event_id)"
=
_is_not_null.txt (text/plain, 1.3K) download
QUERY PLAN
"Nested Loop (cost=395.03..6833.12 rows=1 width=84)"
" -> Nested Loop (cost=395.03..6827.36 rows=1 width=62)"
" -> Hash Join (cost=395.03..6700.72 rows=21 width=70)"
" Hash Cond: ((""outer"".snumber = ""inner"".snumber) AND (""outer"".event_id = ""inner"".event_id))"
" -> Hash Join (cost=1.10..5822.46 rows=6916 width=54)"
" Hash Cond: (""outer"".field_id = ""inner"".field_id)"
" -> Seq Scan on seats (cost=0.00..4887.80 rows=172880 width=24)"
" -> Hash (cost=1.08..1.08 rows=8 width=46)"
" -> Seq Scan on fields (cost=0.00..1.08 rows=8 width=46)"
" -> Hash (cost=393.33..393.33 rows=121 width=24)"
" -> Index Scan using speed_3 on history (cost=0.00..393.33 rows=121 width=24)"
" Index Cond: (type_id = 6)"
" Filter: (undo_action_id IS NOT NULL)"
" -> Index Scan using i_action_id2 on actions (cost=0.00..6.02 rows=1 width=8)"
" Index Cond: (""outer"".action_id = actions.action_id)"
" Filter: (((created)::date = '2005-02-24'::date) AND (user_id = 23))"
" -> Index Scan using events_pkey on events (cost=0.00..5.75 rows=1 width=46)"
" Index Cond: (""outer"".event_id = events.event_id)"
=
_greater_than_null.txt (text/plain, 1.3K) download
QUERY PLAN
"Nested Loop (cost=394.93..6477.86 rows=1 width=84)"
" -> Nested Loop (cost=394.93..6472.10 rows=1 width=62)"
" -> Hash Join (cost=394.93..6423.85 rows=8 width=70)"
" Hash Cond: ((""outer"".snumber = ""inner"".snumber) AND (""outer"".event_id = ""inner"".event_id))"
" -> Hash Join (cost=1.10..5822.46 rows=6916 width=54)"
" Hash Cond: (""outer"".field_id = ""inner"".field_id)"
" -> Seq Scan on seats (cost=0.00..4887.80 rows=172880 width=24)"
" -> Hash (cost=1.08..1.08 rows=8 width=46)"
" -> Seq Scan on fields (cost=0.00..1.08 rows=8 width=46)"
" -> Hash (cost=393.63..393.63 rows=41 width=24)"
" -> Index Scan using speed_3 on history (cost=0.00..393.63 rows=41 width=24)"
" Index Cond: (type_id = 6)"
" Filter: (undo_action_id > 0)"
" -> Index Scan using i_action_id2 on actions (cost=0.00..6.02 rows=1 width=8)"
" Index Cond: (""outer"".action_id = actions.action_id)"
" Filter: (((created)::date = '2005-02-24'::date) AND (user_id = 23))"
" -> Index Scan using events_pkey on events (cost=0.00..5.75 rows=1 width=46)"
" Index Cond: (""outer"".event_id = events.event_id)"
=
query.sql (application/octet-stream, 555B) download
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: IS NULL vs IS NOT NULL
@ 2005-02-25 14:56 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2005-02-25 14:56 UTC (permalink / raw)
To: Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>; +Cc: pgsql-performance
On Fri, 25 Feb 2005, Vig, Sandor (G/FI-2) wrote:
> Hi,
>
> Another "funny" thing: I have a query which runs
> on (Linux) PostgreSQL 7.4.x under 10 sec. I tried
> to run it on (Windows) PostgreSQL 8.0 yesterday.
> It didn't finished at all! (I shoot it down after 10 minutes)
> I made various tests and I figured out something interesting:
> The same query with:
> A, "history.undo_action_id > 0" runs in 10 sec.
> B, "history.undo_action_id is not null" runs in 10 sec.
> C, "history.undo_action_id is null" runs forever (?!)
> I used EXPLAIN but I couldn't figure out what the problem was.
EXPLAIN ANALYZE would be more useful. My first guess would be that the IS
NULL is returning many more than the estimated 1 row and as such a nested
loop is a bad plan. How many history rows match type_id=6 and
undo_action_id is null?
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-02-25 14:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-25 10:10 IS NULL vs IS NOT NULL Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
2005-02-25 14:56 ` Stephan Szabo <sszabo@megazone.bigpanda.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