Strange postgres planner behaviour - Mailing list pgsql-hackers

From Sergey E. Koposov
Subject Strange postgres planner behaviour
Date
Msg-id Pine.LNX.4.44.0503130421370.20522-100000@lnfm1.sai.msu.ru
Whole thread Raw
Responses Re: Strange postgres planner behaviour  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
Hi All, 

I want to descibe some strange behaviour of the postgres planner.


I have 2 tables:

wsdb=# \d q3c    Table "public.q3c"Column |  Type  | Modifiers 
--------+--------+-----------ipix   | bigint | errbox | box    | ra     | real   | dec    | real   | 
Indexes:   "ipix_idx" btree (ipix) CLUSTER   "rtree_ind" rtree (errbox)


And the other table 

wsdb=# \d q3c_subset  Table "public.q3c_subset"Column |  Type  | Modifiers 
--------+--------+-----------ipix   | bigint | errbox | box    | ra     | real   | dec    | real   | 


When I run  the following query, the plan for it is index scan, and it's ok.  


wsdb=# EXPLAIN SELECT  *  FROM q3c_subset AS uu,q3c WHERE 
(q3c.ipix>uu.dec::bigint AND q3c.ipix<uu.ra::bigint) AND
(q3c.ipix>uu.ra::bigint AND q3c.ipix<uu.dec::bigint) ;


QUERY PLAN                                                                                    

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Nested
Loop (cost=0.01..270564956.56 rows=4221207699 width=96)  ->  Seq Scan on q3c_subset uu  (cost=0.00..2314.72 rows=113972
width=48) ->  Index Scan using ipix_idx on q3c  (cost=0.01..1262.80 rows=37038
 
width=48)        Index Cond: ((q3c.ipix > ("outer"."dec")::bigint) AND (q3c.ipix <
("outer".ra)::bigint) AND (q3c.ipix > ("outer".ra)::bigint) AND (q3c.ipix <
("outer"."dec")::bigint))
(4 rows)


But, when in my query I replace one "AND" to "OR" (see below), I have the
sequential scan. BUT THIS IS NOT the reason why I wrote this letter, the
main surprising thing is that even if I "set enable_seq_scan to off" the
plan for new query is still seq. scan!!! So the planner don't even consider
the index scan plan in that case (see below).



wsdb=# EXPLAIN SELECT  *  FROM q3c_subset AS uu,q3c WHERE 
(q3c.ipix>uu.dec::bigint AND q3c.ipix<uu.ra::bigint) OR
(q3c.ipix>uu.ra::bigint AND q3c.ipix<uu.dec::bigint) ;


QUERY PLAN                                                                                           

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Nested
Loop (cost=2428.69..13676776298.93 rows=71760530869 width=96)  Join Filter: ((("outer".ipix > ("inner"."dec")::bigint)
AND("outer".ipix
 
< ("inner".ra)::bigint)) OR (("outer".ipix > ("inner".ra)::bigint) AND
("outer".ipix < ("inner"."dec")::bigint)))  ->  Seq Scan on q3c  (cost=0.00..60928.16 rows=3000016 width=48)  ->
Materialize (cost=2428.69..3568.41 rows=113972 width=48)        ->  Seq Scan on q3c_subset uu  (cost=0.00..2314.72
rows=113972
width=48)
(5 rows)


wsdb=# set enable_seqscan TO off;
SET

wsdb=# EXPLAIN SELECT  *  FROM q3c_subset AS uu,q3c WHERE 
(q3c.ipix>uu.dec::bigint AND q3c.ipix<uu.ra::bigint) OR
(q3c.ipix>uu.ra::bigint AND q3c.ipix<uu.dec::bigint) ;

QUERY PLAN                                                                                           

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Nested
Loop (cost=200002428.69..13876776298.93 rows=71760530869 width=96)  Join Filter: ((("outer".ipix >
("inner"."dec")::bigint)AND ("outer".ipix
 
< ("inner".ra)::bigint)) OR (("outer".ipix > ("inner".ra)::bigint) AND
("outer".ipix < ("inner"."dec")::bigint)))  ->  Seq Scan on q3c  (cost=100000000.00..100060928.16 rows=3000016
width=48)  ->  Materialize  (cost=100002428.69..100003568.41 rows=113972 width=48)        ->  Seq Scan on q3c_subset uu
(cost=100000000.00..100002314.72
 
rows=113972 width=48)
(5 rows)


I tried this queries on 7.4.6 and 8.0.1 and the result is the same. 

So, Why the planner cannot use the index scan for that case ? 
What is wrong ? 

Thank you in advance.

Sergey

------------------------------------------------------------
Sergey E. Koposov
Sternberg Astronomical Institute, Moscow University (Russia)
Max-Planck Institute for Astronomy (Germany) 
Internet: math@sai.msu.ru, http://lnfm1.sai.msu.su/~math/






pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: REL8_0_STABLE and 8.0.1 release client logging difference
Next
From: Tom Lane
Date:
Subject: Re: Strange postgres planner behaviour