Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT .. WHERE id IN(..)

SELECT .. WHERE id IN(..)

From
Maks Materkov
Date:

I have a database, table "users", with column "profile_id", and the following query:


EXPLAIN ANALYZE SELECT * FROM users_user WHERE profile_id IN (...50 ids...);


Result:


Index Scan using users_user_83a0eb3f on users_user  (cost=0.50..292.22 rows=50 

width=633) (actual time=0.039..0.622 rows=44 loops=1)                           

   Index Cond: (profile_id = ANY ('{2445564,... 50 ids....}'::integer[]))

     Planning time: 0.322 ms                                                        

     Execution time: 5192.321 ms

This query takes ~5 seconds. (about ~5 million rows in table). I have a btree index on this column. Is there any way to speed up query?

Re: SELECT .. WHERE id IN(..)

From
Samuel Smith
Date:
On 05/16/2015 10:44 PM, Maks Materkov wrote:
> I have a database, table "users", with column "profile_id", and the
> following query:
>
>
> EXPLAIN ANALYZE SELECT * FROM users_user WHERE profile_id IN (...50 ids...);
>
>
> Result:
>
>
> Index Scan using users_user_83a0eb3f on users_user  (cost=0.50..292.22
> rows=50
>
> width=633) (actual time=0.039..0.622 rows=44 loops=1)
>
>     Index Cond: (profile_id = ANY ('{2445564,... 50 ids....}'::integer[]))
>
>       Planning time: 0.322 ms
>
>       Execution time: 5192.321 ms
>
> This query takes ~5 seconds. (about ~5 million rows in table). I have a
> btree index on this column. Is there any way to speed up query?
>

That does not seem right. Try again only using 'EXPLAIN (ANALYZE
1,BUFFERS 1)'
Also what version are you on and what are shared_buffers set to?

--Sam

Re: SELECT .. WHERE id IN(..)

From
William Dunn
Date:
Hello Maks,

As I think Sam suspects the issue might be that you may not have enough RAM, or not enough RAM is allocated to shared_buffers, or you may have this table's data being evicted from shared_buffers because of some other queries, so while you are identifying all the rows in your fast index scan retrieving the records from disk is very slow. You might want to investigate your shared buffers like so:

Buffer hit rate for the table:
SELECT heap_blks_hit/(heap_blks_hit+heap_blks_read) AS buffer_hit_ratio
FROM pg_statio_user_tables
WHERE relname='users';

Buffer hit rate for the db:
SELECT heap_blks_hit/(heap_blks_hit+heap_blks_read) AS buffer_hit_ratio
FROM pg_statio_user_tables;

Get the current shared buffers setting:
SELECT current_setting('shared_buffers');

If you are going to use 'EXPLAIN (ANALYZE 1,BUFFERS 1)' as Sam suggested it might be good to use some different commonly accessed profile_ids than the ones you just used because those ones will be more likely to be in buffers than usual (since you just ran the same query via explain analyze)


Will J. Dunn

On Sun, May 17, 2015 at 12:49 AM, Samuel Smith <pgsql@net153.net> wrote:
On 05/16/2015 10:44 PM, Maks Materkov wrote:
I have a database, table "users", with column "profile_id", and the
following query:


EXPLAIN ANALYZE SELECT * FROM users_user WHERE profile_id IN (...50 ids...);


Result:


Index Scan using users_user_83a0eb3f on users_user  (cost=0.50..292.22
rows=50

width=633) (actual time=0.039..0.622 rows=44 loops=1)

    Index Cond: (profile_id = ANY ('{2445564,... 50 ids....}'::integer[]))

      Planning time: 0.322 ms

      Execution time: 5192.321 ms

This query takes ~5 seconds. (about ~5 million rows in table). I have a
btree index on this column. Is there any way to speed up query?


That does not seem right. Try again only using 'EXPLAIN (ANALYZE 1,BUFFERS 1)'
Also what version are you on and what are shared_buffers set to?

--Sam


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