Search Postgresql Archives

Re: Recheck condition

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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      

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux