0

I have two tables Places and Redemptions (details in gist)

https://gist.github.com/kunashir/2648e6e4d8a97aa2d03859b8ee08a394

And the query takes a lot of time:

SQL (26910.2ms)  SELECT "redemptions"."id" AS t0_r0,
"redemptions"."uuid" AS t0_r1, "redemptions"."code_value_id" AS t0_r2,
"redemptions"."created_at" AS t0_r3, "redemptions"."updated_at" AS t0_r4,
"redemptions"."place_id" AS t0_r5, "redemptions"."user_id" AS t0_r6,
"places"."id" AS t1_r0, "places"."name" AS t1_r1, "places"."geo_fence" AS t1_r2,
"places"."external_id" AS t1_r3, "places"."city" AS t1_r4,
"places"."country" AS t1_r5, "places"."bounding_box_north" AS t1_r6,
"places"."bounding_box_south" AS t1_r7, "places"."bounding_box_east" AS t1_r8,
"places"."bounding_box_west" AS t1_r9, "places"."image" AS t1_r10,
"places"."created_at" AS t1_r11, "places"."updated_at" AS t1_r12, 
"places"."latitude" AS t1_r13, "places"."longitude" AS t1_r14,
"places"."image_uid" AS t1_r15, "places"."image_name" AS t1_r16,
"places"."featured" AS t1_r17, "places"."active_benefits_count" AS t1_r18,
"places"."radius" AS t1_r19, "places"."terminal" AS t1_r20,
"places"."deleted_at" AS t1_r21, "places"."category" AS t1_r22,
"places"."street" AS t1_r23, "places"."zip" AS t1_r24,
"places"."twitter" AS t1_r25, "places"."push_notification_message" AS t1_r26,
"places"."timezone" AS t1_r27, "places"."twitter_boost" AS t1_r28,
"places"."push_notification_message_enabled" AS t1_r29,
"places"."geojson" AS t1_r30, "places"."push_notification_image_uid" AS t1_r31,
"places"."searchable" AS t1_r32, "places"."boost" AS t1_r33,
"places"."geojson_active" AS t1_r34 
FROM "redemptions" LEFT OUTER JOIN "places" ON "places"."id" = "redemptions"."place_id" AND "places"."deleted_at" IS NULL

The result of explain: https://explain.depesz.com/s/PYPC

I can't understand when is bottleneck or all is right?

Improve this question
2
  • 1
    The query itself only takes 82 milli seconds (0.082 seconds) - I wouldn't call that "a lot of time". Where do you see the SQL (26910.2ms) timing information? If that is the timing your SQL client is showing you, then that includes the time to send 46375 rows over the network and process them in your SQL client
    – user1822
    Commented May 15, 2018 at 14:36
  • @a_horse_with_no_name yes You are right. It's data was from Rails console. I've changed select statement and it works good now. Thanks. (if You will added answer I accept it how useful).
    – kunashir
    Commented May 16, 2018 at 6:30

1 Answer 1

Reset to default
0

The query itself only takes 82 milliseconds (0.082 seconds).

The reported 26 seconds are either caused by the network or by the application (or SQL client) processing and displaying the result. As far as I can tell, it's highly unlikely that it's the network as the 46375 rows add up to roughly 30KB of transmitted data.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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