Need to know how to preserve postgreSQL index when using a func
- or -
Post a project like this- Posted:
- Proposals: 2
- Remote
- #2587243
- PRE-FUNDED
- Awarded
Description
create function apiv1_private.toArray2(value json) returns text[] as
$toArray$
SELECT ARRAY(SELECT json_array_elements_text(value));
$toArray$ language sql;
--------
...
WHERE utm_campaign = ANY( apiv1_private.toArray('[
"BILLETTERIE1920PAID",
"BILLETTERIE-1819-PAID",
"BILLETTERIE-1920-PAID",
"BILLETTERIE1819PAID"
]')::text[])
This is fast (use my index on utm_campaign):
...
WHERE utm_campaign = ANY( ARRAY(SELECT json_array_elements_text('[
"BILLETTERIE1920PAID",
"BILLETTERIE-1819-PAID",
"BILLETTERIE-1920-PAID",
"BILLETTERIE1819PAID"
]'))::text[])
How to be able to wrap code into function, use the function as parameter and preserve the index
Slow query explain:
QUERY PLAN
Planning Time: 2.672 ms
Execution Time: 156748.073 ms
Aggregate (cost=3375669.26..3375669.27 rows=1 width=64) (actual time=156739.829..156739.829 rows=1 loops=1)
-> Unique (cost=3375343.44..3375442.19 rows=18165 width=548) (actual time=156723.294..156738.308 rows=24410 loops=1)
-> Sort (cost=3375343.44..3375392.82 rows=19751 width=548) (actual time=156723.291..156729.736 rows=43821 loops=1)
Sort Method: quicksort Memory: 4960kB
Sort Key: ((raw_tracks.anonymous_id)::text)
-> Bitmap Heap Scan on raw_tracks_next raw_tracks (cost=48577.32..3373934.24 rows=19751 width=548) (actual time=1354.989..156573.115 rows=43821 loops=1)
Rows Removed by Filter: 1304489
Recheck Cond: (((pixel_key)::text = '111'::text) AND ((event)::text = 'pageview'::text) AND ("timestamp" >= '2019-09-23 05:00:00.661+00'::timestamp with time zone) AND ("timestamp" = '2019-09-23 05:00:00.661+00'::timestamp with time zone) AND ("timestamp" = '2019-09-23 05:00:00.661+00'::timestamp with time zone) AND ("timestamp" = '2019-09-23 05:00:00.661+00'::timestamp with time zone) AND ("timestamp" = '2019-09-23 05:00:00.661+00'::timestamp with time zone) AND ("timestamp" = '2019-09-23 05:00:00.661+00'::timestamp with time zone) AND ("timestamp" <= '2019-10-24 04:59:59.661+00'::timestamp with time zone))
ZAMBAUX S.
0% (0)New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
could you please add explain and explain analyze for both queries?
ZAMBAUX S.24 Oct 2019Sur, this is the slow one:
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('Planning Time: 2.672 ms');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('Execution Time: 156748.073 ms');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('Aggregate (cost=3375669.26..3375669.27 rows=1 width=64) (actual time=156739.829..156739.829 rows=1 loops=1)');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' -> Unique (cost=3375343.44..3375442.19 rows=18165 width=548) (actual time=156723.294..156738.308 rows=24410 loops=1)');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' -> Sort (cost=3375343.44..3375392.82 rows=19751 width=548) (actual time=156723.291..156729.736 rows=43821 loops=1)');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' Sort Method: quicksort Memory: 4960kB');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' Sort Key: ((raw_tracks.anonymous_id)::text)');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' -> Bitmap Heap Scan on raw_tracks_next raw_tracks (cost=48577.32..3373934.24 rows=19751 width=548) (actual time=1354.989..156573.115 rows=43821 loops=1)');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' Rows Removed by Filter: 1304489');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' Recheck Cond: (((pixel_key)::text = ''11''::text) AND ((event)::text = ''pageview''::text) AND ("timestamp" >= ''2019-09-23 05:00:00.661+00''::timestamp with time zone) AND ("timestamp" <= ''2019-10-24 04:59:59.661+00''::timestamp with time zone))');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' Heap Blocks: exact=1172753');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' Filter: ((utm_campaign)::text = ANY (apiv1_private.toarray(''[');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' -> Bitmap Index Scan on pixel_key_event_time_utm_campaign_raw_tracks_next (cost=0.00..48572.38 rows=972779 width=0) (actual time=737.829..737.829 rows=1348310 loops=1)');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' Index Cond: (((pixel_key)::text = ''11''::text) AND ((event)::text = ''pageview''::text) AND ("timestamp" >= ''2019-09-23 05:00:00.661+00''::timestamp with time zone) AND ("timestamp" <= ''2019-10-24 04:59:59.661+00''::timestamp with time zone))');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' ]''::json)))');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' "BILLETTERIE1920PAID",');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' "BILLETTERIE1819PAID"');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' "BILLETTERIE-1920-PAID",');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' "BILLETTERIE-1819-PAID",');