SELECT .. WHERE id IN(..)

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

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*
*willjdunn.com <http://willjdunn.com&gt;*

On Sun, May 17, 2015 at 12:49 AM, Samuel Smith <pgsql@net153.net> wrote: