pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect in subselect vs select = any array
8+ messages / 4 participants
[nested] [flat]
* Select in subselect vs select = any array
@ 2011-03-20 06:47 Adam Tistler <atistler@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Adam Tistler @ 2011-03-20 06:47 UTC (permalink / raw)
To: pgsql-performance
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-perl
CentOS release 5.4 (Final)
psql (PostgreSQL) 8.4.1
prompt2=# select count(*) from nodes;
count
--------
754734
(1 row)
prompt2=# \d nodes
Table "public.nodes"
Column | Type | Modifiers
--------------+--------------------------+-----------------------------------------------------------
node_id | integer | not null default nextval(('node_id_seq'::text)::regclass)
node_type_id | integer | not null
template_id | integer | not null
timestamp | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
Indexes:
"nodes_pkey" PRIMARY KEY, btree (node_id)
"n_node_id_index" btree (node_id)
"n_node_type_id_index" btree (node_type_id)
"n_template_id_index" btree (template_id)
prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
count
--------
100000
(1 row)
Time: 404.530 ms
prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
count
--------
100000
(1 row)
Time: 407.316 ms
prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
count
--------
100000
(1 row)
Time: 408.728 ms
prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
count
--------
100000
(1 row)
Time: 793.840 ms
prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
count
--------
100000
(1 row)
Time: 779.137 ms
prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
count
--------
100000
(1 row)
Time: 781.820 ms
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Select in subselect vs select = any array
@ 2011-03-20 06:51 Pavel Stehule <pavel.stehule@gmail.com>
parent: Adam Tistler <atistler@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Pavel Stehule @ 2011-03-20 06:51 UTC (permalink / raw)
To: Adam Tistler <atistler@gmail.com>; +Cc: pgsql-performance
Hello
2011/3/20 Adam Tistler <atistler@gmail.com>:
> 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 EXPLAIN ANALYZE SELECT ..., please
The reasons can be different - less seq scans, indexes
Regards
Pavel Stehule
> ./configure --prefix=/usr/local/pgsql84 --with-openssl --with-perl
> CentOS release 5.4 (Final)
> psql (PostgreSQL) 8.4.1
>
> prompt2=# select count(*) from nodes;
> count
> --------
> 754734
> (1 row)
>
>
> prompt2=# \d nodes
> Table "public.nodes"
> Column | Type | Modifiers
> --------------+--------------------------+-----------------------------------------------------------
> node_id | integer | not null default nextval(('node_id_seq'::text)::regclass)
> node_type_id | integer | not null
> template_id | integer | not null
> timestamp | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
> Indexes:
> "nodes_pkey" PRIMARY KEY, btree (node_id)
> "n_node_id_index" btree (node_id)
> "n_node_type_id_index" btree (node_type_id)
> "n_template_id_index" btree (template_id)
>
> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
> count
> --------
> 100000
> (1 row)
>
> Time: 404.530 ms
> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
> count
> --------
> 100000
> (1 row)
>
> Time: 407.316 ms
> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
> count
> --------
> 100000
> (1 row)
>
> Time: 408.728 ms
> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
> count
> --------
> 100000
> (1 row)
>
> Time: 793.840 ms
> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
> count
> --------
> 100000
> (1 row)
>
> Time: 779.137 ms
> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
> count
> --------
> 100000
> (1 row)
>
> Time: 781.820 ms
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Select in subselect vs select = any array
@ 2011-03-21 03:20 Adam Tistler <atistler@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Adam Tistler @ 2011-03-21 03:20 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-performance
logicops2=# explain analyze select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1718.59..1718.60 rows=1 width=0) (actual time=509.126..509.127 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..1637.04 rows=100000 width=4) (actual time=0.010..76.604 rows=100000 loops=1)
-> Seq Scan on nodes (cost=0.00..12355.41 rows=754741 width=4) (actual time=0.008..38.105 rows=100000 loops=1)
-> Bitmap Heap Scan on nodes (cost=42.67..81.53 rows=10 width=0) (actual time=447.274..484.283 rows=100000 loops=1)
Recheck Cond: (node_id = ANY ($0))
-> Bitmap Index Scan on n_node_id_index (cost=0.00..42.67 rows=10 width=0) (actual time=447.074..447.074 rows=100000 loops=1)
Index Cond: (node_id = ANY ($0))
Total runtime: 509.209 ms
(9 rows)
Time: 510.009 ms
logicops2=# explain analyze select count(*) from nodes where node_id in (select node_id from nodes limit 100000);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3017.17..3017.18 rows=1 width=0) (actual time=1052.866..1052.866 rows=1 loops=1)
-> Nested Loop (cost=2887.04..3016.67 rows=200 width=0) (actual time=167.310..1021.540 rows=100000 loops=1)
-> HashAggregate (cost=2887.04..2889.04 rows=200 width=4) (actual time=167.198..251.205 rows=100000 loops=1)
-> Limit (cost=0.00..1637.04 rows=100000 width=4) (actual time=0.008..80.090 rows=100000 loops=1)
-> Seq Scan on nodes (cost=0.00..12355.41 rows=754741 width=4) (actual time=0.007..41.566 rows=100000 loops=1)
-> Index Scan using n_node_id_index on nodes (cost=0.00..0.63 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=100000)
Index Cond: (public.nodes.node_id = public.nodes.node_id)
Total runtime: 1053.523 ms
(8 rows)
Time: 1054.864 ms
On Mar 20, 2011, at 2:51 AM, Pavel Stehule wrote:
> Hello
>
> 2011/3/20 Adam Tistler <atistler@gmail.com>:
>> 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 EXPLAIN ANALYZE SELECT ..., please
>
> The reasons can be different - less seq scans, indexes
>
> Regards
>
> Pavel Stehule
>
>
>
>> ./configure --prefix=/usr/local/pgsql84 --with-openssl --with-perl
>> CentOS release 5.4 (Final)
>> psql (PostgreSQL) 8.4.1
>>
>> prompt2=# select count(*) from nodes;
>> count
>> --------
>> 754734
>> (1 row)
>>
>>
>> prompt2=# \d nodes
>> Table "public.nodes"
>> Column | Type | Modifiers
>> --------------+--------------------------+-----------------------------------------------------------
>> node_id | integer | not null default nextval(('node_id_seq'::text)::regclass)
>> node_type_id | integer | not null
>> template_id | integer | not null
>> timestamp | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
>> Indexes:
>> "nodes_pkey" PRIMARY KEY, btree (node_id)
>> "n_node_id_index" btree (node_id)
>> "n_node_type_id_index" btree (node_type_id)
>> "n_template_id_index" btree (template_id)
>>
>> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>> count
>> --------
>> 100000
>> (1 row)
>>
>> Time: 404.530 ms
>> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>> count
>> --------
>> 100000
>> (1 row)
>>
>> Time: 407.316 ms
>> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>> count
>> --------
>> 100000
>> (1 row)
>>
>> Time: 408.728 ms
>> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
>> count
>> --------
>> 100000
>> (1 row)
>>
>> Time: 793.840 ms
>> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
>> count
>> --------
>> 100000
>> (1 row)
>>
>> Time: 779.137 ms
>> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
>> count
>> --------
>> 100000
>> (1 row)
>>
>> Time: 781.820 ms
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Select in subselect vs select = any array
@ 2011-03-21 05:54 Pavel Stehule <pavel.stehule@gmail.com>
parent: Adam Tistler <atistler@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Pavel Stehule @ 2011-03-21 05:54 UTC (permalink / raw)
To: Adam Tistler <atistler@gmail.com>; +Cc: pgsql-performance
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 <atistler@gmail.com>:
> logicops2=# explain analyze select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=1718.59..1718.60 rows=1 width=0) (actual time=509.126..509.127 rows=1 loops=1)
> InitPlan 1 (returns $0)
> -> Limit (cost=0.00..1637.04 rows=100000 width=4) (actual time=0.010..76.604 rows=100000 loops=1)
> -> Seq Scan on nodes (cost=0.00..12355.41 rows=754741 width=4) (actual time=0.008..38.105 rows=100000 loops=1)
> -> Bitmap Heap Scan on nodes (cost=42.67..81.53 rows=10 width=0) (actual time=447.274..484.283 rows=100000 loops=1)
> Recheck Cond: (node_id = ANY ($0))
> -> Bitmap Index Scan on n_node_id_index (cost=0.00..42.67 rows=10 width=0) (actual time=447.074..447.074 rows=100000 loops=1)
> Index Cond: (node_id = ANY ($0))
> Total runtime: 509.209 ms
> (9 rows)
>
> Time: 510.009 ms
>
>
> logicops2=# explain analyze select count(*) from nodes where node_id in (select node_id from nodes limit 100000);
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=3017.17..3017.18 rows=1 width=0) (actual time=1052.866..1052.866 rows=1 loops=1)
> -> Nested Loop (cost=2887.04..3016.67 rows=200 width=0) (actual time=167.310..1021.540 rows=100000 loops=1)
> -> HashAggregate (cost=2887.04..2889.04 rows=200 width=4) (actual time=167.198..251.205 rows=100000 loops=1)
> -> Limit (cost=0.00..1637.04 rows=100000 width=4) (actual time=0.008..80.090 rows=100000 loops=1)
> -> Seq Scan on nodes (cost=0.00..12355.41 rows=754741 width=4) (actual time=0.007..41.566 rows=100000 loops=1)
> -> Index Scan using n_node_id_index on nodes (cost=0.00..0.63 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=100000)
> Index Cond: (public.nodes.node_id = public.nodes.node_id)
> Total runtime: 1053.523 ms
> (8 rows)
>
> Time: 1054.864 ms
>
>
>
> On Mar 20, 2011, at 2:51 AM, Pavel Stehule wrote:
>
>> Hello
>>
>> 2011/3/20 Adam Tistler <atistler@gmail.com>:
>>> 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 EXPLAIN ANALYZE SELECT ..., please
>>
>> The reasons can be different - less seq scans, indexes
>>
>> Regards
>>
>> Pavel Stehule
>>
>>
>>
>>> ./configure --prefix=/usr/local/pgsql84 --with-openssl --with-perl
>>> CentOS release 5.4 (Final)
>>> psql (PostgreSQL) 8.4.1
>>>
>>> prompt2=# select count(*) from nodes;
>>> count
>>> --------
>>> 754734
>>> (1 row)
>>>
>>>
>>> prompt2=# \d nodes
>>> Table "public.nodes"
>>> Column | Type | Modifiers
>>> --------------+--------------------------+-----------------------------------------------------------
>>> node_id | integer | not null default nextval(('node_id_seq'::text)::regclass)
>>> node_type_id | integer | not null
>>> template_id | integer | not null
>>> timestamp | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
>>> Indexes:
>>> "nodes_pkey" PRIMARY KEY, btree (node_id)
>>> "n_node_id_index" btree (node_id)
>>> "n_node_type_id_index" btree (node_type_id)
>>> "n_template_id_index" btree (template_id)
>>>
>>> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>>> count
>>> --------
>>> 100000
>>> (1 row)
>>>
>>> Time: 404.530 ms
>>> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>>> count
>>> --------
>>> 100000
>>> (1 row)
>>>
>>> Time: 407.316 ms
>>> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>>> count
>>> --------
>>> 100000
>>> (1 row)
>>>
>>> Time: 408.728 ms
>>> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
>>> count
>>> --------
>>> 100000
>>> (1 row)
>>>
>>> Time: 793.840 ms
>>> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
>>> count
>>> --------
>>> 100000
>>> (1 row)
>>>
>>> Time: 779.137 ms
>>> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
>>> count
>>> --------
>>> 100000
>>> (1 row)
>>>
>>> Time: 781.820 ms
>>>
>>>
>>> --
>>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Select in subselect vs select = any array
@ 2011-03-21 06:16 Adam Tistler <atistler@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Adam Tistler @ 2011-03-21 06:16 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-performance
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 found that even for small queries, the following is faster:
select * from nodes where node_in = any (array[1,2,3 .... ])
Its not really a big deal to me, I was just wondering if others could reproduce it on other systems/versions and if perhaps this is an issue that I should point out to postgres-dev.
Results below:
logicops2=# explain analyze select count(*) from nodes where node_id in ( select node_id from nodes limit 100000 );
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3017.18..3017.19 rows=1 width=0) (actual time=1017.051..1017.051 rows=1 loops=1)
-> Nested Loop (cost=2887.05..3016.68 rows=200 width=0) (actual time=157.290..986.329 rows=100000 loops=1)
-> HashAggregate (cost=2887.05..2889.05 rows=200 width=4) (actual time=157.252..241.995 rows=100000 loops=1)
-> Limit (cost=0.00..1637.05 rows=100000 width=4) (actual time=0.009..73.942 rows=100000 loops=1)
-> Seq Scan on nodes (cost=0.00..12355.34 rows=754734 width=4) (actual time=0.008..35.428 rows=100000 loops=1)
-> Index Scan using n_node_id_index on nodes (cost=0.00..0.63 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=100000)
Index Cond: (public.nodes.node_id = public.nodes.node_id)
Total runtime: 1017.794 ms
(8 rows)
logicops2=# explain analyze select count(*) from nodes where node_id = any(array ( select node_id from nodes limit 100000 ));
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1718.60..1718.61 rows=1 width=0) (actual time=485.554..485.555 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..1637.05 rows=100000 width=4) (actual time=0.011..73.037 rows=100000 loops=1)
-> Seq Scan on nodes (cost=0.00..12355.34 rows=754734 width=4) (actual time=0.010..34.462 rows=100000 loops=1)
-> Bitmap Heap Scan on nodes (cost=42.67..81.53 rows=10 width=0) (actual time=433.003..461.108 rows=100000 loops=1)
Recheck Cond: (node_id = ANY ($0))
-> Bitmap Index Scan on n_node_id_index (cost=0.00..42.67 rows=10 width=0) (actual time=432.810..432.810 rows=100000 loops=1)
Index Cond: (node_id = ANY ($0))
Total runtime: 485.638 ms
(9 rows)
On Mar 21, 2011, at 1:54 AM, Pavel Stehule wrote:
> 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 <atistler@gmail.com>:
>> logicops2=# explain analyze select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>> QUERY PLAN
>> -----------------------------------------------------------------------------------------------------------------------------------------
>> Aggregate (cost=1718.59..1718.60 rows=1 width=0) (actual time=509.126..509.127 rows=1 loops=1)
>> InitPlan 1 (returns $0)
>> -> Limit (cost=0.00..1637.04 rows=100000 width=4) (actual time=0.010..76.604 rows=100000 loops=1)
>> -> Seq Scan on nodes (cost=0.00..12355.41 rows=754741 width=4) (actual time=0.008..38.105 rows=100000 loops=1)
>> -> Bitmap Heap Scan on nodes (cost=42.67..81.53 rows=10 width=0) (actual time=447.274..484.283 rows=100000 loops=1)
>> Recheck Cond: (node_id = ANY ($0))
>> -> Bitmap Index Scan on n_node_id_index (cost=0.00..42.67 rows=10 width=0) (actual time=447.074..447.074 rows=100000 loops=1)
>> Index Cond: (node_id = ANY ($0))
>> Total runtime: 509.209 ms
>> (9 rows)
>>
>> Time: 510.009 ms
>>
>>
>> logicops2=# explain analyze select count(*) from nodes where node_id in (select node_id from nodes limit 100000);
>> QUERY PLAN
>> ----------------------------------------------------------------------------------------------------------------------------------------
>> Aggregate (cost=3017.17..3017.18 rows=1 width=0) (actual time=1052.866..1052.866 rows=1 loops=1)
>> -> Nested Loop (cost=2887.04..3016.67 rows=200 width=0) (actual time=167.310..1021.540 rows=100000 loops=1)
>> -> HashAggregate (cost=2887.04..2889.04 rows=200 width=4) (actual time=167.198..251.205 rows=100000 loops=1)
>> -> Limit (cost=0.00..1637.04 rows=100000 width=4) (actual time=0.008..80.090 rows=100000 loops=1)
>> -> Seq Scan on nodes (cost=0.00..12355.41 rows=754741 width=4) (actual time=0.007..41.566 rows=100000 loops=1)
>> -> Index Scan using n_node_id_index on nodes (cost=0.00..0.63 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=100000)
>> Index Cond: (public.nodes.node_id = public.nodes.node_id)
>> Total runtime: 1053.523 ms
>> (8 rows)
>>
>> Time: 1054.864 ms
>>
>>
>>
>> On Mar 20, 2011, at 2:51 AM, Pavel Stehule wrote:
>>
>>> Hello
>>>
>>> 2011/3/20 Adam Tistler <atistler@gmail.com>:
>>>> 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 EXPLAIN ANALYZE SELECT ..., please
>>>
>>> The reasons can be different - less seq scans, indexes
>>>
>>> Regards
>>>
>>> Pavel Stehule
>>>
>>>
>>>
>>>> ./configure --prefix=/usr/local/pgsql84 --with-openssl --with-perl
>>>> CentOS release 5.4 (Final)
>>>> psql (PostgreSQL) 8.4.1
>>>>
>>>> prompt2=# select count(*) from nodes;
>>>> count
>>>> --------
>>>> 754734
>>>> (1 row)
>>>>
>>>>
>>>> prompt2=# \d nodes
>>>> Table "public.nodes"
>>>> Column | Type | Modifiers
>>>> --------------+--------------------------+-----------------------------------------------------------
>>>> node_id | integer | not null default nextval(('node_id_seq'::text)::regclass)
>>>> node_type_id | integer | not null
>>>> template_id | integer | not null
>>>> timestamp | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
>>>> Indexes:
>>>> "nodes_pkey" PRIMARY KEY, btree (node_id)
>>>> "n_node_id_index" btree (node_id)
>>>> "n_node_type_id_index" btree (node_type_id)
>>>> "n_template_id_index" btree (template_id)
>>>>
>>>> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>>>> count
>>>> --------
>>>> 100000
>>>> (1 row)
>>>>
>>>> Time: 404.530 ms
>>>> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>>>> count
>>>> --------
>>>> 100000
>>>> (1 row)
>>>>
>>>> Time: 407.316 ms
>>>> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>>>> count
>>>> --------
>>>> 100000
>>>> (1 row)
>>>>
>>>> Time: 408.728 ms
>>>> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
>>>> count
>>>> --------
>>>> 100000
>>>> (1 row)
>>>>
>>>> Time: 793.840 ms
>>>> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
>>>> count
>>>> --------
>>>> 100000
>>>> (1 row)
>>>>
>>>> Time: 779.137 ms
>>>> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
>>>> count
>>>> --------
>>>> 100000
>>>> (1 row)
>>>>
>>>> Time: 781.820 ms
>>>>
>>>>
>>>> --
>>>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>>
>>
>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Select in subselect vs select = any array
@ 2011-03-21 06:38 Pavel Stehule <pavel.stehule@gmail.com>
parent: Adam Tistler <atistler@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2011-03-21 06:38 UTC (permalink / raw)
To: Adam Tistler <atistler@gmail.com>; +Cc: pgsql-performance
2011/3/21 Adam Tistler <atistler@gmail.com>:
> 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 found that even for small queries, the following is faster:
> select * from nodes where node_in = any (array[1,2,3 .... ])
it depends on version. I think so on last postgres, these queries are
same, not sure.
Regards
Pavel
>
>
> Its not really a big deal to me, I was just wondering if others could reproduce it on other systems/versions and if perhaps this is an issue that I should point out to postgres-dev.
>
>
> Results below:
>
> logicops2=# explain analyze select count(*) from nodes where node_id in ( select node_id from nodes limit 100000 );
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=3017.18..3017.19 rows=1 width=0) (actual time=1017.051..1017.051 rows=1 loops=1)
> -> Nested Loop (cost=2887.05..3016.68 rows=200 width=0) (actual time=157.290..986.329 rows=100000 loops=1)
> -> HashAggregate (cost=2887.05..2889.05 rows=200 width=4) (actual time=157.252..241.995 rows=100000 loops=1)
> -> Limit (cost=0.00..1637.05 rows=100000 width=4) (actual time=0.009..73.942 rows=100000 loops=1)
> -> Seq Scan on nodes (cost=0.00..12355.34 rows=754734 width=4) (actual time=0.008..35.428 rows=100000 loops=1)
> -> Index Scan using n_node_id_index on nodes (cost=0.00..0.63 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=100000)
> Index Cond: (public.nodes.node_id = public.nodes.node_id)
> Total runtime: 1017.794 ms
> (8 rows)
>
> logicops2=# explain analyze select count(*) from nodes where node_id = any(array ( select node_id from nodes limit 100000 ));
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=1718.60..1718.61 rows=1 width=0) (actual time=485.554..485.555 rows=1 loops=1)
> InitPlan 1 (returns $0)
> -> Limit (cost=0.00..1637.05 rows=100000 width=4) (actual time=0.011..73.037 rows=100000 loops=1)
> -> Seq Scan on nodes (cost=0.00..12355.34 rows=754734 width=4) (actual time=0.010..34.462 rows=100000 loops=1)
> -> Bitmap Heap Scan on nodes (cost=42.67..81.53 rows=10 width=0) (actual time=433.003..461.108 rows=100000 loops=1)
> Recheck Cond: (node_id = ANY ($0))
> -> Bitmap Index Scan on n_node_id_index (cost=0.00..42.67 rows=10 width=0) (actual time=432.810..432.810 rows=100000 loops=1)
> Index Cond: (node_id = ANY ($0))
> Total runtime: 485.638 ms
> (9 rows)
>
> On Mar 21, 2011, at 1:54 AM, Pavel Stehule wrote:
>
>> 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 <atistler@gmail.com>:
>>> logicops2=# explain analyze select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>>> QUERY PLAN
>>> -----------------------------------------------------------------------------------------------------------------------------------------
>>> Aggregate (cost=1718.59..1718.60 rows=1 width=0) (actual time=509.126..509.127 rows=1 loops=1)
>>> InitPlan 1 (returns $0)
>>> -> Limit (cost=0.00..1637.04 rows=100000 width=4) (actual time=0.010..76.604 rows=100000 loops=1)
>>> -> Seq Scan on nodes (cost=0.00..12355.41 rows=754741 width=4) (actual time=0.008..38.105 rows=100000 loops=1)
>>> -> Bitmap Heap Scan on nodes (cost=42.67..81.53 rows=10 width=0) (actual time=447.274..484.283 rows=100000 loops=1)
>>> Recheck Cond: (node_id = ANY ($0))
>>> -> Bitmap Index Scan on n_node_id_index (cost=0.00..42.67 rows=10 width=0) (actual time=447.074..447.074 rows=100000 loops=1)
>>> Index Cond: (node_id = ANY ($0))
>>> Total runtime: 509.209 ms
>>> (9 rows)
>>>
>>> Time: 510.009 ms
>>>
>>>
>>> logicops2=# explain analyze select count(*) from nodes where node_id in (select node_id from nodes limit 100000);
>>> QUERY PLAN
>>> ----------------------------------------------------------------------------------------------------------------------------------------
>>> Aggregate (cost=3017.17..3017.18 rows=1 width=0) (actual time=1052.866..1052.866 rows=1 loops=1)
>>> -> Nested Loop (cost=2887.04..3016.67 rows=200 width=0) (actual time=167.310..1021.540 rows=100000 loops=1)
>>> -> HashAggregate (cost=2887.04..2889.04 rows=200 width=4) (actual time=167.198..251.205 rows=100000 loops=1)
>>> -> Limit (cost=0.00..1637.04 rows=100000 width=4) (actual time=0.008..80.090 rows=100000 loops=1)
>>> -> Seq Scan on nodes (cost=0.00..12355.41 rows=754741 width=4) (actual time=0.007..41.566 rows=100000 loops=1)
>>> -> Index Scan using n_node_id_index on nodes (cost=0.00..0.63 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=100000)
>>> Index Cond: (public.nodes.node_id = public.nodes.node_id)
>>> Total runtime: 1053.523 ms
>>> (8 rows)
>>>
>>> Time: 1054.864 ms
>>>
>>>
>>>
>>> On Mar 20, 2011, at 2:51 AM, Pavel Stehule wrote:
>>>
>>>> Hello
>>>>
>>>> 2011/3/20 Adam Tistler <atistler@gmail.com>:
>>>>> 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 EXPLAIN ANALYZE SELECT ..., please
>>>>
>>>> The reasons can be different - less seq scans, indexes
>>>>
>>>> Regards
>>>>
>>>> Pavel Stehule
>>>>
>>>>
>>>>
>>>>> ./configure --prefix=/usr/local/pgsql84 --with-openssl --with-perl
>>>>> CentOS release 5.4 (Final)
>>>>> psql (PostgreSQL) 8.4.1
>>>>>
>>>>> prompt2=# select count(*) from nodes;
>>>>> count
>>>>> --------
>>>>> 754734
>>>>> (1 row)
>>>>>
>>>>>
>>>>> prompt2=# \d nodes
>>>>> Table "public.nodes"
>>>>> Column | Type | Modifiers
>>>>> --------------+--------------------------+-----------------------------------------------------------
>>>>> node_id | integer | not null default nextval(('node_id_seq'::text)::regclass)
>>>>> node_type_id | integer | not null
>>>>> template_id | integer | not null
>>>>> timestamp | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
>>>>> Indexes:
>>>>> "nodes_pkey" PRIMARY KEY, btree (node_id)
>>>>> "n_node_id_index" btree (node_id)
>>>>> "n_node_type_id_index" btree (node_type_id)
>>>>> "n_template_id_index" btree (template_id)
>>>>>
>>>>> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>>>>> count
>>>>> --------
>>>>> 100000
>>>>> (1 row)
>>>>>
>>>>> Time: 404.530 ms
>>>>> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>>>>> count
>>>>> --------
>>>>> 100000
>>>>> (1 row)
>>>>>
>>>>> Time: 407.316 ms
>>>>> prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
>>>>> count
>>>>> --------
>>>>> 100000
>>>>> (1 row)
>>>>>
>>>>> Time: 408.728 ms
>>>>> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
>>>>> count
>>>>> --------
>>>>> 100000
>>>>> (1 row)
>>>>>
>>>>> Time: 793.840 ms
>>>>> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
>>>>> count
>>>>> --------
>>>>> 100000
>>>>> (1 row)
>>>>>
>>>>> Time: 779.137 ms
>>>>> prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
>>>>> count
>>>>> --------
>>>>> 100000
>>>>> (1 row)
>>>>>
>>>>> Time: 781.820 ms
>>>>>
>>>>>
>>>>> --
>>>>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>>>>> To make changes to your subscription:
>>>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>>>
>>>
>>>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Select in subselect vs select = any array
@ 2011-03-22 02:55 mark <dvlhntr@gmail.com>
parent: Adam Tistler <atistler@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: mark @ 2011-03-22 02:55 UTC (permalink / raw)
To: 'Adam Tistler' <atistler@gmail.com>; 'Pavel Stehule' <pavel.stehule@gmail.com>; +Cc: pgsql-performance
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
> owner@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] Select in subselect vs select = any array
>
> 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 ..... )
>
What does "large" number of primary keys mean ?
I have seen some "odd" things happen when I passed, carelessly, tens of
thousands of items to an in list for a generated query, but I don't get the
feeling that isn't the case here.
..: Mark
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Select in subselect vs select = any array
@ 2011-04-18 18:27 Robert Haas <robertmhaas@gmail.com>
parent: Adam Tistler <atistler@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Robert Haas @ 2011-04-18 18:27 UTC (permalink / raw)
To: Adam Tistler <atistler@gmail.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-performance
On Sun, Mar 20, 2011 at 11:20 PM, Adam Tistler <atistler@gmail.com> wrote:
> logicops2=# explain analyze select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=1718.59..1718.60 rows=1 width=0) (actual time=509.126..509.127 rows=1 loops=1)
> InitPlan 1 (returns $0)
> -> Limit (cost=0.00..1637.04 rows=100000 width=4) (actual time=0.010..76.604 rows=100000 loops=1)
> -> Seq Scan on nodes (cost=0.00..12355.41 rows=754741 width=4) (actual time=0.008..38.105 rows=100000 loops=1)
> -> Bitmap Heap Scan on nodes (cost=42.67..81.53 rows=10 width=0) (actual time=447.274..484.283 rows=100000 loops=1)
> Recheck Cond: (node_id = ANY ($0))
> -> Bitmap Index Scan on n_node_id_index (cost=0.00..42.67 rows=10 width=0) (actual time=447.074..447.074 rows=100000 loops=1)
> Index Cond: (node_id = ANY ($0))
> Total runtime: 509.209 ms
> (9 rows)
>
> Time: 510.009 ms
>
>
> logicops2=# explain analyze select count(*) from nodes where node_id in (select node_id from nodes limit 100000);
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=3017.17..3017.18 rows=1 width=0) (actual time=1052.866..1052.866 rows=1 loops=1)
> -> Nested Loop (cost=2887.04..3016.67 rows=200 width=0) (actual time=167.310..1021.540 rows=100000 loops=1)
> -> HashAggregate (cost=2887.04..2889.04 rows=200 width=4) (actual time=167.198..251.205 rows=100000 loops=1)
> -> Limit (cost=0.00..1637.04 rows=100000 width=4) (actual time=0.008..80.090 rows=100000 loops=1)
> -> Seq Scan on nodes (cost=0.00..12355.41 rows=754741 width=4) (actual time=0.007..41.566 rows=100000 loops=1)
> -> Index Scan using n_node_id_index on nodes (cost=0.00..0.63 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=100000)
> Index Cond: (public.nodes.node_id = public.nodes.node_id)
> Total runtime: 1053.523 ms
> (8 rows)
>
> Time: 1054.864 ms
This is a pretty interesting example. I think this is just an
optimizer limitation.
When trying to build a join tree (in this case, between the copy of
nodes inside the subselect and the copy outside the subselect), the
planner considers three main join strategies: hash join, nested loop,
merge join. A merge or hash join will have to read the
outside-the-subselect copy of nodes in its entirety (I think); the
only way to avoid that is to compute the subselect first and then use
the index probes to pull out just the matching rows. That's what the
planner did in both cases, but in the second case it's not smart
enough to see that it can gather up all the values from the inner side
of the join and shove them into a bitmap index scan all at once, so it
just uses a regular index scan to pull 'em out one at a time.
I think this would be pretty tricky to support, since the join node
would need to understand all the parameter passing that needs to
happen between the inner and outer sides of the loop; it's almost like
a whole new join type.
You might also want to make the opposite transformation, turning the
first plan into the second one, if (for example) the subselect is
going to return a gigabyte of data. But we're just not that smart.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-04-18 18:27 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-20 06:47 Select in subselect vs select = any array Adam Tistler <atistler@gmail.com>
2011-03-20 06:51 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-03-21 03:20 ` Adam Tistler <atistler@gmail.com>
2011-03-21 05:54 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-03-21 06:16 ` Adam Tistler <atistler@gmail.com>
2011-03-21 06:38 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-03-22 02:55 ` mark <dvlhntr@gmail.com>
2011-04-18 18:27 ` Robert Haas <robertmhaas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox