Postgresql, very long joins

explainjoin;postgresql

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?

Best Answer

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.

Related Solutions

MySQL: Very slow query, not using index, no joins involved

You may want the following indexes

ALTER TABLE tran.member ADD INDEX cardnbr_mbr_nbr_ndx (cardnbr,mbr_nbr);
ALTER TABLE tran.member ADD INDEX mbr_nbr_cardnbr_ndx (mbr_nbr,cardnbr);

Then, you could queries like these

Each Member and All Cards for That Member

SELECT mbr_nbr MemberNumber,GROUP_CONCAT(cardnbr) Cards
FROM tran.member GROUP BY mbr_nbr;

Each Card and All Members Associated with that Card

SELECT cardnbr CardNumber,GROUP_CONCAT(mbr_nbr) Members
FROM tran.member GROUP BY cardnbr;

So, that the size of tran.member does not slow things down, try creating a temp table for the purpose of gathering these two columns

DROP TABLE IF EXISTS tran.adhoc_members_cards;
CREATE TABLE tran.adhoc_members_cards
SELECT mbr_nbr,cardnbr FROM tran.member WHERE 1=2;
ALTER TABLE tran.adhoc_members_cards ENGINE=MyISAM;
ALTER TABLE tran.adhoc_members_cards ADD INDEX cardnbr_mbr_nbr_ndx (cardnbr,mbr_nbr);
ALTER TABLE tran.adhoc_members_cards ADD INDEX mbr_nbr_cardnbr_ndx (mbr_nbr,cardnbr);
ALTER TABLE tran.adhoc_members_cards DISABLE KEYS;
INSERT INTO tran.adhoc_members_cards
SELECT mbr_nbr,cardnbr FROM tran.memberl
SELECT mbr_nbr,cardnbr FROM tran.member WHERE 1=2;
ALTER TABLE tran.adhoc_members_cards ENABLE KEYS;

Now, you can run the two queries I mentioned like this:

SELECT mbr_nbr MemberNumber,GROUP_CONCAT(cardnbr) Cards
FROM tran.adhoc_members_cards GROUP BY mbr_nbr;
SELECT cardnbr CardNumber,GROUP_CONCAT(mbr_nbr) Members
FROM tran.adhoc_members_cards GROUP BY cardnbr;

Search for the cardmember you want as well:

SELECT cardnbr CardNumber,GROUP_CONCAT(mbr_nbr) Members
FROM tran.adhoc_members_cards WHERE cardnbr = 000099999930;

PostgreSQL – Troubleshooting Slow Query Execution

     Sort Method: external merge  Disk: 92048kB

Throw more work_mem at the problem. Lots more. Try:

SET LOCAL work_mem = '300MB';

Be aware that if you're running it in lots of concurrent connections you could exhaust system RAM. So SET only in individual sessions.

Your row-count estimate on the aggregate is a bit dodgy (http://explain.depesz.com/s/RXbq) but not too bad. The killer seems to be that big sort.

Best Answer

Related Solutions

MySQL: Very slow query, not using index, no joins involved

PostgreSQL – Troubleshooting Slow Query Execution

Related Question