Re: Recheck condition

On Nov 30, 2007 7:55 AM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 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.90rows=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.006rows=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.189rows=327498 loops=1)
Index Cond: ((column1 = 1) AND
((column2)::text =
'62'::text))
-> Subquery Scan "*SELECT* 2"
(cost=156754.24..713053.77rows=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.919rows=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.644rows=3429228 loops=1)
Index Cond: ((column1 = 1) AND
((column2)::text =
'189'::text))
Total runtime: 45504.425 ms