Postgresql IN operator Performance: List vs Subquery -


for list of ~700 ids query performance on 20x slower passing subquery returns 700 ids. should opposite.

e.g. (first query takes under 400ms, later 9600 ms)

select date_trunc('month', day) month, sum(total) table_x y_id in (select id table_y prop = 'xyz')  , day between '2015-11-05' , '2016-11-04'  group month 

is 20x faster on machine passing array directly:

select date_trunc('month', day) month, sum(total)  table_x y_id in (1625, 1871, ..., 1640, 1643, 13291, 1458, 13304, 1407, 1765)  , day between '2015-11-05' , '2016-11-04'  group month  

any idea problem or how optimize , obtain same performance?

the difference simple filter vs hash join:

explain analyze select  t in (500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600);                                               query plan                                                                                                                                                                                                                         ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  seq scan on t  (cost=0.00..140675.00 rows=101 width=4) (actual time=0.648..1074.567 rows=101 loops=1)    filter: (i = ('{500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600}'::integer[]))    rows removed filter: 999899  planning time: 0.170 ms  execution time: 1074.624 ms  explain analyze select t in (select r);                                                     query plan                                                      -------------------------------------------------------------------------------------------------------------------  hash semi join  (cost=3.27..17054.40 rows=101 width=4) (actual time=0.382..240.389 rows=101 loops=1)    hash cond: (t.i = r.i)    ->  seq scan on t  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.030..117.193 rows=1000000 loops=1)    ->  hash  (cost=2.01..2.01 rows=101 width=4) (actual time=0.074..0.074 rows=101 loops=1)          buckets: 1024  batches: 1  memory usage: 12kb          ->  seq scan on r  (cost=0.00..2.01 rows=101 width=4) (actual time=0.010..0.035 rows=101 loops=1)  planning time: 0.245 ms  execution time: 240.448 ms 

to have same performance join array:

explain analyze select     t     inner join     unnest(         array[500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600]::int[]     ) u (i) using (i) ;                                                       query plan                                                        -----------------------------------------------------------------------------------------------------------------------  hash join  (cost=2.25..18178.25 rows=100 width=4) (actual time=0.267..243.768 rows=101 loops=1)    hash cond: (t.i = u.i)    ->  seq scan on t  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.022..118.709 rows=1000000 loops=1)    ->  hash  (cost=1.00..1.00 rows=100 width=4) (actual time=0.063..0.063 rows=101 loops=1)          buckets: 1024  batches: 1  memory usage: 12kb          ->  function scan on unnest u  (cost=0.00..1.00 rows=100 width=4) (actual time=0.028..0.041 rows=101 loops=1)  planning time: 0.172 ms  execution time: 243.816 ms 

or use values syntax:

explain analyze select  t = (values (500),(501),(502),(503),(504),(505),(506),(507),(508),(509),(510),(511),(512),(513),(514),(515),(516),(517),(518),(519),(520),(521),(522),(523),(524),(525),(526),(527),(528),(529),(530),(531),(532),(533),(534),(535),(536),(537),(538),(539),(540),(541),(542),(543),(544),(545),(546),(547),(548),(549),(550),(551),(552),(553),(554),(555),(556),(557),(558),(559),(560),(561),(562),(563),(564),(565),(566),(567),(568),(569),(570),(571),(572),(573),(574),(575),(576),(577),(578),(579),(580),(581),(582),(583),(584),(585),(586),(587),(588),(589),(590),(591),(592),(593),(594),(595),(596),(597),(598),(599),(600)) ;                                                       query plan                                                        -----------------------------------------------------------------------------------------------------------------------  hash semi join  (cost=2.53..17053.65 rows=101 width=4) (actual time=0.279..239.888 rows=101 loops=1)    hash cond: (t.i = "*values*".column1)    ->  seq scan on t  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.022..117.199 rows=1000000 loops=1)    ->  hash  (cost=1.26..1.26 rows=101 width=4) (actual time=0.059..0.059 rows=101 loops=1)          buckets: 1024  batches: 1  memory usage: 12kb          ->  values scan on "*values*"  (cost=0.00..1.26 rows=101 width=4) (actual time=0.002..0.027 rows=101 loops=1)  planning time: 0.242 ms  execution time: 239.933 ms 

Comments

Popular posts from this blog

java - SSE Emitter : Manage timeouts and complete() -

jquery - uncaught exception: DataTables Editor - remote hosting of code not allowed -

java - How to resolve error - package com.squareup.okhttp3 doesn't exist? -