Re: Recheck condition - Mailing list pgsql-general
| From | Josh Harrison |
|---|---|
| Subject | Re: Recheck condition |
| Date | |
| Msg-id | 8d89ea1d0711300521mc345c9ax4a005e5f0eb7f83b@mail.gmail.com Whole thread Raw |
| In response to | Re: Recheck condition (Alvaro Herrera <alvherre@alvh.no-ip.org>) |
| Responses |
Re: Recheck condition
(Martijn van Oosterhout <kleptog@svana.org>)
|
| List | pgsql-general |
On Nov 30, 2007 7:55 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
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 Josh Harrison escribió:Hmm, I think INTERSECT (and EXCEPT) is pretty stupid in Postgres in
> 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')
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
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
pgsql-general by date: