Re: [PERFORM] Select in subselect vs select = any array

2011-04-18 Thread Robert Haas
On Sun, Mar 20, 2011 at 11:20 PM, Adam Tistler wrote: > logicops2=# explain analyze select count(*) from nodes where node_id = any(   > Array(select node_id from nodes limit 10) ); >                                                               QUERY PLAN >

Re: [PERFORM] Select in subselect vs select = any array

2011-03-21 Thread mark
> -Original Message- > From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- > ow...@postgresql.org] On Behalf Of Adam Tistler > Sent: Monday, March 21, 2011 12:17 AM > To: Pavel Stehule > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM

Re: [PERFORM] Select in subselect vs select = any array

2011-03-20 Thread Pavel Stehule
2011/3/21 Adam Tistler : > Pavel, thanks for the help. > > I increased work_mem from 16MB to 64MB, no difference.  The queries are > really just a test case.  My actual queries are actual just large number of > primary keys that I am selecting from the db: > > For example: >   select * from nodes

Re: [PERFORM] Select in subselect vs select = any array

2011-03-20 Thread Adam Tistler
Pavel, thanks for the help. I increased work_mem from 16MB to 64MB, no difference. The queries are really just a test case. My actual queries are actual just large number of primary keys that I am selecting from the db: For example: select * from nodes where node_id in ( 1, 2, 3 . ) I

Re: [PERFORM] Select in subselect vs select = any array

2011-03-20 Thread Pavel Stehule
Hello I think so HashAggregate goes out of memory - you can try to increase a work_mem. There are better queries for counting duplicit then cross join Regards Pavel Stehule 2011/3/21 Adam Tistler : > logicops2=# explain analyze select count(*) from nodes where node_id = any(   > Array(select n

Re: [PERFORM] Select in subselect vs select = any array

2011-03-20 Thread Adam Tistler
logicops2=# explain analyze select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 10) ); QUERY PLAN -

Re: [PERFORM] Select in subselect vs select = any array

2011-03-19 Thread Pavel Stehule
Hello 2011/3/20 Adam Tistler : > I have noticed that SELECT ... = ANY(ARRAY(...))  is about twice as fast as > SELECT IN ( ... ). > Can anyone explain a reason for this?  Results are the bottom and are > reproducible.  I can test with other versions if that is necessary. > send a result of EXPL

[PERFORM] Select in subselect vs select = any array

2011-03-19 Thread Adam Tistler
I have noticed that SELECT ... = ANY(ARRAY(...)) is about twice as fast as SELECT IN ( ... ). Can anyone explain a reason for this? Results are the bottom and are reproducible. I can test with other versions if that is necessary. ./configure --prefix=/usr/local/pgsql84 --with-openssl --with-p