Re: Recheck condition

["Josh Harrison" <joshques@xxxxxxxxx>]

On Nov 30, 2007 7:55 AM, Alvaro Herrera <alvherre@xxxxxxxxxxxxxx> wrote:

Josh Harrison escribió:

> Thanks...
> I have 1 more question in the same line...
>
> *Query1*
> SELECT person_id  FROM person   WHERE (column1=1 AND column2='62')
> INTERSECT
> SELECT person_id  FROM person  WHERE (column1=1 AND column2='189')

Hmm, I think INTERSECT (and EXCEPT) is pretty stupid in Postgres in
general.  Maybe INTERSECT ALL could be a bit faster, because it can
avoid the sort steps.  Make sure you eliminate duplicates if they are a
concern.

I get the same plan(see below)  with 'sort'  for 'intersect all' operation too. Why is intersect not an effecient way? Is there any other way this query/index can be written/created so that I can get the intersect results in an efficient way?
Thanks
jo

QUERY PLAN                                                                                                                                                                          
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 SetOp Intersect All  (cost=1750719.48..1769378.35 rows=373177 width=4) (actual time=41065.459..45469.038 rows=128562 loops=1)                                                       
   ->  Sort  (cost=1750719.48..1760048.92
 rows=3731774 width=4) (actual time=41065.375..44027.342 rows=3756726 loops=1)                                                              
         Sort Key: "*SELECT* 1".patient_id                                                                                                                                           
         Sort Method:  external merge  Disk: 73432kB                                                                                                                                 
         ->  Append  (cost=17886.42..1209431.67
 rows=3731774 width=4) (actual time=1445.675..30171.066 rows=3756726 loops=1)                                                         
               ->  Subquery Scan "*SELECT* 1"  (cost=17886.42..496377.90
 rows=381993 width=4) (actual time=1445.674..8223.061 rows=327498 loops=1)                                   
                     ->  Bitmap Heap Scan on person   (cost=17886.42..492557.97 rows=381993 width=4) (actual time=
1445.670..8021.006 rows=327498 loops=1)             
                           Recheck Cond: ((column1 = 1) AND ((column2)::text = '62'::text))                                                        
                           ->  Bitmap Index Scan on person_idx  (cost=
0.00..17790.92 rows=381993 width=0) (actual time=1440.189..1440.189 rows=327498 loops=1)    
                                 Index Cond: ((column1 = 1) AND ((column2)::text = '62'::text))                                                    
               ->  Subquery Scan "*SELECT* 2"  (cost=156754.24..713053.77 rows=3349781 width=4) (actual time=4183.977..20195.276 rows=3429228 loops=1)                               
                     ->  Bitmap Heap Scan on person   (cost=
156754.24..679555.96 rows=3349781 width=4) (actual time=4183.973..18191.919 rows=3429228 loops=1)         
                           Recheck Cond: ((column1 = 1) AND ((column2)::text = '189'::text))                                                       
                           ->  Bitmap Index Scan on person_idx  (cost=0.00..155916.80 rows=3349781 width=0) (actual time=4178.644..4178.644 rows=3429228 loops=1) 
                                 Index Cond: ((column1 = 1) AND ((column2)::text = '189'::text))                                                   
 Total runtime: 45504.425 ms