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: [EMAIL PROTECTED], http://lnfm1.sai.msu.su/~math/
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
