[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [HACKERS] performance of bitmap scans in nested loop joins
From:       "Sergey E. Koposov" <math () sai ! msu ! ru>
Date:       2005-04-30 0:01:53
Message-ID: Pine.LNX.4.44.0504300348520.5991-100000 () lnfm1 ! sai ! msu ! ru
[Download RAW message or body]

On Fri, 29 Apr 2005, Tom Lane wrote:
> > ->  Index Scan using ipix_idx on q3c  (cost=0.01..9686.37 rows=333335 width=48) \
> > (actual time=0.006..0.006 rows=0 loops=3000000) Index Cond: ((q3c.ipix >= \
> > ("outer".ipix - 1000)) AND (q3c.ipix <= ("outer".ipix - 993)))
> 
> > ->  Bitmap Index Scan on ipix_idx  (cost=0.00..2916.02 rows=333335 width=0) \
> > (actual time=0.011..0.011 rows=0 loops=3000000) Index Cond: ((q3c.ipix >= \
> > ("outer".ipix - 1000)) AND (q3c.ipix <= ("outer".ipix - 993)))
> 
> The latter is (or should be) doing slightly *less* work, so why is it
> taking almost twice as much time?  Can you get gprof profiles of the
> two cases?


I've got them. Here there are two gprof profiles:

http://lnfm1.sai.msu.ru/~math/public_misc/idxscan.gprof
http://lnfm1.sai.msu.ru/~math/public_misc/bitmap.gprof
  (now as links, because the previous letter with those files as attachements
   haven't passed on -hackers (due to size, I think)) 


bitmap.gprof is  the profiling of the:

test=# explain analyze select * from q3c,q3c as q3cs where 
(q3c.ipix>=q3cs.ipix-3 AND q3c.ipix<=q3cs.ipix+3) OR 
(q3c.ipix>=q3cs.ipix-1000 AND q3c.ipix<=q3cs.ipix-993);
                                                                                
QUERY PLAN                                                                      
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------
 Nested Loop  (cost=5832.01..190280130928.00 rows=1888888888889 width=96)
(actual time=0.435..374743.591 rows=3000000 loops=1)
   ->  Seq Scan on q3c q3cs  (cost=0.00..60928.00 rows=3000000 width=48)
(actual time=0.079..10632.570 rows=3000000 loops=1)
   ->  Bitmap Heap Scan on q3c  (cost=5832.01..43426.68 rows=666667
width=48)
(actual time=0.102..0.104 rows=1 loops=3000000)
         Recheck Cond: (((q3c.ipix >= ("outer".ipix - 3)) AND (q3c.ipix <=
("outer".ipix + 3))) OR ((q3c.ipix >= ("outer".ipix - 1000)) AND (q3c.ipix
<=
("outer".ipix - 993))))
         ->  BitmapOr  (cost=5832.01..5832.01 rows=666667 width=0) (actual
time=0.094..0.094 rows=0 loops=3000000)
               ->  Bitmap Index Scan on ipix_idx  (cost=0.00..2916.01
rows=333333 width=0) (actual time=0.045..0.045 rows=1 loops=3000000)
                     Index Cond: ((q3c.ipix >= ("outer".ipix - 3)) AND
(q3c.ipix <= ("outer".ipix + 3)))
               ->  Bitmap Index Scan on ipix_idx  (cost=0.00..2916.01
rows=333333 width=0) (actual time=0.041..0.041 rows=0 loops=3000000)
                     Index Cond: ((q3c.ipix >= ("outer".ipix - 1000)) AND
(q3c.ipix <= ("outer".ipix - 993)))
 Total runtime: 377551.805 ms
(10 rows)


And idxscan.gprof is the profiling of the:

test=# EXPLAIN ANALYZE SELECT * FROM q3c,q3c as q3cs WHERE 
(q3c.ipix>=q3cs.ipix-1000 AND q3c.ipix<=q3cs.ipix-993);
                                                            QUERY PLAN          
--------------------------------------------------------------------------------
---------------------------------------------------
 Nested Loop  (cost=0.01..49059045928.00 rows=1000000000000 width=96)
(actual
time=104991.950..104991.950 rows=0 loops=1)
   ->  Seq Scan on q3c q3cs  (cost=0.00..60928.00 rows=3000000 width=48)
(actual time=0.069..10465.514 rows=3000000 loops=1)
   ->  Index Scan using ipix_idx on q3c  (cost=0.01..9686.33 rows=333333
width=48) (actual time=0.025..0.025 rows=0 loops=3000000)
         Index Cond: ((q3c.ipix >= ("outer".ipix - 1000)) AND (q3c.ipix <=
("outer".ipix - 993)))
 Total runtime: 104992.202 ms
(5 rows)


With Best regards, 
        Sergey Koposov






---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org


[prev in list] [next in list] [prev in thread] [next in thread]