Re: Select in subselect vs select = any array

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

 



Hello

2011/3/20 Adam Tistler <atistler@xxxxxxxxx>:
> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux