I want to fetch up to 100 rows based on their id. The id is the primary key of the table.

The query that I had written looks like this:

select * from table where id = any ($1);

where $1 is interpolated as an array of ids.

When using EXPLAIN ANALYZE I get the following plan (explain link):

                                                                QUERY PLAN
 Limit  (cost=0.43..44.98 rows=17 width=553) (actual time=100.048..834.209 rows=17 loops=1)
   ->  Index Scan using instagram_id_index_1000 on profiles_1000  (cost=0.43..44.98 rows=17 width=553) (actual time=100.046..834.163 rows=17 loops=1)
         Index Cond: (id = ANY ('{34491540,28977916,33241270,33609141,31043380,29364420,30247037,33311491,36267571,32886281,32366574,32569254,33038689,31089076,29416100,30455309,31570597}'::integer[]))
 Planning time: 424.512 ms
 Execution time: 834.280 ms
(5 rows)

When I actually execute it (with \timing) I get results in the 2-5 seconds range! I really cannot wrap my mind around such abysmal performance. The execution time provided by EXPLAIN ANALYZE was already high in the first place.

Some context:
1) the database is local, so there is no network latency
2) the table I'm querying on is a materialized view
3) I also tried the where id in (...) variation and nothing changed
4) I tried to programmatically loop over the ids and run a separate query for each and it produces better results (around 1.5s)

Is there something that can be done here? I cannot believe that Postgres' performance in my case is this bad. The server also has 8 cores, is it possible to parallelize this query as much as possible?

  • Have you tried joining against the values? – LowlyDBA Nov 7 '18 at 18:46
  • 3
    The only thing that would be included in \timing that's not in the server-generated query execution time would be serializing the results and deserializing in psql; are any of the column values very large, on the order of 100+kb? – AdamKG Nov 7 '18 at 19:21
  • @AdamKG Unfortunately not, the rows are very small. Here are the stats: gist.github.com/rubik/b197f85d8c5c84daf309d397562dbffe, I used the query from this answer – rubik Nov 8 '18 at 2:43
  • @LowlyDBA Thanks for the suggestion, I'll try that approach – rubik Nov 8 '18 at 2:44
  • Run the query in a tight loop (using \watch 0.001, for example) and then use an OS tool such as "top" to see what is using the time, "psql" itself or the postgres backend it is attached to. – jjanes Nov 11 '18 at 23:23

I seem to recall reading something around this area recently, out of date statistics can cause a discrepancy between the estimated and actual execution plan activity, and there was another point made about table variables being interpreted as a single row rather than however many items the variable\array actually contains. Hope that helps.

  • In this case the actual and est rows are the same, though, so it doesn't seem like an issue with statistics. – LowlyDBA Nov 7 '18 at 20:20
  • good point Lowly – roachw Nov 7 '18 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.