9

Im trying to better understand how the query planner work in postgresql.

I have this query:

select id from users 
    where id <> 2
    and gender = (select gender from users where id = 2)
    order by latest_location::geometry <-> (select latest_location from users where id = 2) ASC
    limit 50

It runs in less than 10ms on my database with around 500k entries in the users table.

Then I thought that to avoid the duplicate subselects I could rewrite the query as a CTE, like this:

with me as (
    select * from users where id = 2
)
select u.id, u.popularity from users u, me 
    where u.gender = me.gender
    order by  u.latest_location::geometry <-> me.latest_location::geometry ASC
    limit 50;

However, this rewritten query runs in around 1 sec! Why does this happen? I see in the explains that it doesnt use the geometry index, but can anything be done for that? Thanks!

Another way to write the query is:

select u.id, u.popularity from users u, (select gender, latest_location from users where id = 2) as me 
    where u.gender = me.gender
    order by  u.latest_location::geometry <-> me.latest_location::geometry ASC
    limit 50;

However, this will also be as slow as the CTE.

If on the other hand I extract out the me parameters and statically insert them the query is quick again:

select u.id, u.popularity from users u
    where u.gender = 'male'
    order by  u.latest_location::geometry <-> '0101000000A49DE61DA71C5A403D0AD7A370F54340'::geometry ASC
    limit 50;

Explain of the first (fast) query

 Limit  (cost=5.69..20.11 rows=50 width=36) (actual time=0.512..8.114 rows=50 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using users_pkey on users users_1  (cost=0.42..2.64 rows=1 width=32) (actual time=0.032..0.033 rows=1 loops=1)
           Index Cond: (id = 2)
   InitPlan 2 (returns $1)
     ->  Index Scan using users_pkey on users users_2  (cost=0.42..2.64 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)
           Index Cond: (id = 2)
   ->  Index Scan using users_latest_location_gix on users  (cost=0.41..70796.51 rows=245470 width=36) (actual time=0.509..8.100 rows=50 loops=1)
         Order By: (latest_location <-> $0)
         Filter: (gender = $1)
         Rows Removed by Filter: 20
 Total runtime: 8.211 ms
(12 rows)

Explain of the second (slow) query

Limit  (cost=62419.82..62419.95 rows=50 width=76) (actual time=1024.963..1024.970 rows=50 loops=1)
   CTE me
     ->  Index Scan using users_pkey on users  (cost=0.42..2.64 rows=1 width=221) (actual time=0.037..0.038 rows=1 loops=1)
           Index Cond: (id = 2)
   ->  Sort  (cost=62417.18..63030.86 rows=245470 width=76) (actual time=1024.959..1024.963 rows=50 loops=1)
         Sort Key: ((u.latest_location <-> me.latest_location))
         Sort Method: top-N heapsort  Memory: 28kB
         ->  Hash Join  (cost=0.03..54262.85 rows=245470 width=76) (actual time=0.122..938.131 rows=288646 loops=1)
               Hash Cond: (u.gender = me.gender)
               ->  Seq Scan on users u  (cost=0.00..49353.41 rows=490941 width=48) (actual time=0.021..465.025 rows=490994 loops=1)
               ->  Hash  (cost=0.02..0.02 rows=1 width=36) (actual time=0.054..0.054 rows=1 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 1kB
                     ->  CTE Scan on me  (cost=0.00..0.02 rows=1 width=36) (actual time=0.047..0.049 rows=1 loops=1)
 Total runtime: 1025.096 ms
  • 3
    I wrote about this recently; see blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences . Though there are currently some DNS issues that may limit reachability of that site. Try a subquery in FROM instead of the CTE term for best results. – Craig Ringer Dec 10 '14 at 4:03
  • what if you use (select id, latest_location from users where id = 2) as the cte? Maybe it is the * that is causing this issue – cha Dec 10 '14 at 4:33
  • I would have thought that you'd look for the closest users of opposite gender :) – cha Dec 10 '14 at 4:34
  • @cha Makes no difference in speed to just select gender and location in the cte. (In my case I want to take the average of similar users, just that i simplified the query for the question) – viblo Dec 10 '14 at 4:46
  • @CraigRinger I dont think its the optimization fence. I also tried your suggestion and it was also slow. On the other hand, if I extract the parameters manually it is quick (and its a real option in my case, the end result is a function anyway). – viblo Dec 10 '14 at 4:51
9

Try this:

with me as (
    select * from users where id = 2
)
select u.id, u.popularity from users u, me 
    where u.gender = (select gender from me)
    order by  u.latest_location::geometry <-> (select latest_location from me)::geometry ASC
    limit 50;

When I look at the fast plan here's what jumps out at me (bolded):

 Limit  (cost=5.69..20.11 rows=50 width=36) (actual time=0.512..8.114 rows=50 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using users_pkey on users users_1  (cost=0.42..2.64 rows=1 width=32) (actual time=0.032..0.033 rows=1 loops=1)
           Index Cond: (id = 2)
   InitPlan 2 (returns $1)
     ->  Index Scan using users_pkey on users users_2  (cost=0.42..2.64 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)
           Index Cond: (id = 2)
   ->  Index Scan using users_latest_location_gix on users  (cost=0.41..70796.51 rows=245470 width=36) (actual time=0.509..8.100 rows=50 loops=1)
         Order By: (latest_location  $0)
         Filter: (gender = $1)
         Rows Removed by Filter: 20
 Total runtime: 8.211 ms
(12 rows)

In the slow version, the query planner is evaluating the equality operator on gender and the geometry operator on latest_location in the context of a join, where the value from me could vary with each row (even though it has correctly estimated only 1 row). In the fast version the values of gender and latest_location are treated as scalars because they are emitted by inline subqueries, which tells the query planner it only has one value of each to deal with. This is the same reason why you get the fast plan when you paste the literal values.

  • I think you can remove me from the from clause now. – Jarius Hebzo Aug 1 '18 at 16:42

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.