Postgres中的查询速度较慢，同时连接具有10M行的所有连接列索引的表 [英] Slow query in Postgres while joining tables with 10M rows all join column indexed
本文介绍了Postgres中的查询速度较慢，同时连接具有10M行的所有连接列索引的表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有三个表，分别是10M、120M和7M(域名、公司和技术)。我离开了加入COMPANY和TECHING表，域名在id列。ID列在所有三个表上都是btree索引的。我根据过滤(代理键列)从中选择1,000行，该列也是btree索引的。
select d.domain_id,m.id,c.*,t.*
from
prospects_v6.domain_info d
left join master.m_all_tags m using(tag_id)
left join prospects_v6.tech_info t on t.domain_id = d.domain_id
left join prospects_v6.company_info c on c.domain_id = d.domain_id
where
d.domain_id is not null
and d.prospector_id in (
'20881',
'94259',
'232564',
'86408',
'189655',
'10654',
'12916674',
'12816939',
'136487',
'137920',
'41978',
'29876',
'88698',
'92173',
'211585',
'139373',
'89140',
'5006',
'39649',
'83666',
'136533',
'10949',
'12812242',
'81684',
'11873',
'139736',
'216078',
'192526',
'89299',
'154159',
'89204',
'93744',
'99332',
'136447',
'12832241',
'82964',
'137684',
'81011',
'86456',
'47800',
'89787',
'138707',
'86821',
'88741',
'139209',
'89695',
'10929',
'89580',
'92113',
'248744',
'93381',
'137100',
'13422',
'135922',
'139672',
'89625',
'32440',
'138471',
'139002',
'12886918',
'139481',
'137298',
'221742',
'93941',
'82996',
'12862710',
'134327',
'136711',
'117993',
'84739',
'209561',
'25124',
'12907898',
'156867',
'25313',
'205498',
'162553',
'89648',
'137976',
'137767',
'137968',
'137779',
'12861976',
'139083',
'137579',
'176214',
'87495',
'86364',
'243901',
'12828901',
'92564',
'139901',
'90791',
'136781',
'139073',
'12861423',
'12815159',
'100902',
'137628',
'139911',
'137316'
) order by c.domain
EXPLAIN ANALYSE for the query仅在ENABLE_HASHJOIN=ON时使用hashjoin，在关闭优化器时使用合并和嵌套循环联接。 enable_hashjoin=off；
Gather Merge (cost=24678052.73..25181243.43 rows=4312762 width=1646) (actual time=43027.778..43115.778 rows=101 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=24677052.71..24682443.66 rows=2156381 width=1646) (actual time=42592.810..42592.826 rows=34 loops=3)
Sort Key: c.cmp_web_traff_rank, c.cmp_domain
Sort Method: quicksort Memory: 136kB
Worker 0: Sort Method: quicksort Memory: 169kB
Worker 1: Sort Method: quicksort Memory: 142kB
-> Nested Loop Left Join (cost=3906925.51..19828928.98 rows=2156381 width=1646) (actual time=41688.783..42592.598 rows=34 loops=3)
-> Merge Left Join (cost=3906925.07..3926857.09 rows=2156381 width=501) (actual time=41688.730..42591.754 rows=34 loops=3)
Merge Cond: (d.domain_id = c.domain_id)
-> Sort (cost=2771941.43..2777332.38 rows=2156381 width=221) (actual time=19427.296..19427.317 rows=34 loops=3)
Sort Key: d.domain_id
Sort Method: quicksort Memory: 50kB
Worker 0: Sort Method: quicksort Memory: 56kB
Worker 1: Sort Method: quicksort Memory: 52kB
-> Merge Left Join (cost=2052810.47..2088114.20 rows=2156381 width=221) (actual time=19342.801..19419.225 rows=34 loops=3)
Merge Cond: (d.tag_id = m.tag_id)
-> Sort (cost=1967900.66..1973291.61 rows=2156381 width=193) (actual time=18860.436..18860.448 rows=34 loops=3)
Sort Key: d.tag_id
Sort Method: quicksort Memory: 40kB
Worker 0: Sort Method: quicksort Memory: 43kB
Worker 1: Sort Method: quicksort Memory: 42kB
-> Parallel Seq Scan on domain_info d (cost=0.00..1328299.43 rows=2156381 width=193) (actual time=900.630..18860.238 rows=34 loops=3)
Filter: ((domain_id IS NOT NULL) AND (prospector_id = ANY ('{20881,94259,232564,86408,189655,10654,12916674,12816939,136487,137920,41978,29876,88698,92173,211585,139373,89140,5006,39649,83666,136533,10949,12812242,81684,11873,139736,216078,192526,89299,154159,89204,93744,99332,136447,12832241,82964,137684,81011,86456,47800,89787,138707,86821,88741,139209,89695,10929,89580,92113,248744,93381,137100,13422,135922,139672,89625,32440,138471,139002,12886918,139481,137298,221742,93941,82996,12862710,134327,136711,117993,84739,209561,25124,12907898,156867,25313,205498,162553,89648,137976,137767,137968,137779,12861976,139083,137579,176214,87495,86364,243901,12828901,92564,139901,90791,136781,139073,12861423,12815159,100902,137628,139911,137316}'::integer[])))
Rows Removed by Filter: 3433181
-> Sort (cost=84909.81..86388.82 rows=591603 width=36) (actual time=452.739..522.965 rows=261724 loops=3)
Sort Key: m.tag_id
Sort Method: external merge Disk: 43144kB
Worker 0: Sort Method: external merge Disk: 43144kB
Worker 1: Sort Method: external merge Disk: 43144kB
-> Seq Scan on m_all_tags m (cost=0.00..12015.03 rows=591603 width=36) (actual time=0.023..124.137 rows=262143 loops=3)
-> Sort (cost=1134983.65..1138208.40 rows=1289903 width=288) (actual time=22248.525..22870.536 rows=920674 loops=3)
Sort Key: c.domain_id
Sort Method: external merge Disk: 385312kB
Worker 0: Sort Method: external merge Disk: 385312kB
Worker 1: Sort Method: external merge Disk: 385312kB
-> Seq Scan on company_info c (cost=0.00..660170.03 rows=1289903 width=288) (actual time=0.053..13601.667 rows=1264805 loops=3)
-> Index Scan using tech_info_domain_id_key on tech_info t (cost=0.43..7.37 rows=1 width=1128) (actual time=0.015..0.015 rows=1 loops=101)
Index Cond: (domain_id = d.domain_id)
Planning Time: 0.482 ms
Execution Time: 43173.777 ms
enable_hashjoin=on；
Gather Merge (cost=11228582.27..11731772.97 rows=4312762 width=1646) (actual time=155494.221..156547.658 rows=101 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=11227582.25..11232973.20 rows=2156381 width=1646) (actual time=155487.311..155487.583 rows=34 loops=3)
Sort Key: c.cmp_web_traff_rank, c.cmp_domain
Sort Method: quicksort Memory: 149kB
Worker 0: Sort Method: quicksort Memory: 137kB
Worker 1: Sort Method: quicksort Memory: 161kB
-> Parallel Hash Left Join (cost=3727608.55..6379458.52 rows=2156381 width=1646) (actual time=120612.707..155487.262 rows=34 loops=3)
Hash Cond: (d.domain_id = t.domain_id)
-> Parallel Hash Left Join (cost=693405.12..2303913.60 rows=2156381 width=501) (actual time=32426.682..32845.415 rows=34 loops=3)
Hash Cond: (d.domain_id = c.domain_id)
-> Parallel Hash Left Join (cost=13571.28..1467385.25 rows=2156381 width=221) (actual time=19376.508..19444.711 rows=34 loops=3)
Hash Cond: (d.tag_id = m.tag_id)
-> Parallel Seq Scan on domain_info d (cost=0.00..1328299.43 rows=2156381 width=193) (actual time=1082.026..19255.014 rows=34 loops=3)
Filter: ((domain_id IS NOT NULL) AND (prospector_id = ANY ('{20881,94259,232564,86408,189655,10654,12916674,12816939,136487,137920,41978,29876,88698,92173,211585,139373,89140,5006,39649,83666,136533,10949,12812242,81684,11873,139736,216078,192526,89299,154159,89204,93744,99332,136447,12832241,82964,137684,81011,86456,47800,89787,138707,86821,88741,139209,89695,10929,89580,92113,248744,93381,137100,13422,135922,139672,89625,32440,138471,139002,12886918,139481,137298,221742,93941,82996,12862710,134327,136711,117993,84739,209561,25124,12907898,156867,25313,205498,162553,89648,137976,137767,137968,137779,12861976,139083,137579,176214,87495,86364,243901,12828901,92564,139901,90791,136781,139073,12861423,12815159,100902,137628,139911,137316}'::integer[])))
Rows Removed by Filter: 3433181
-> Parallel Hash (cost=8564.01..8564.01 rows=246501 width=36) (actual time=106.434..106.435 rows=87381 loops=3)
Buckets: 65536 Batches: 16 Memory Usage: 3648kB
-> Parallel Seq Scan on m_all_tags m (cost=0.00..8564.01 rows=246501 width=36) (actual time=0.015..31.192 rows=87381 loops=3)
-> Parallel Hash (cost=652645.60..652645.60 rows=537460 width=288) (actual time=12962.870..12962.871 rows=421602 loops=3)
Buckets: 16384 Batches: 128 Memory Usage: 3488kB
-> Parallel Seq Scan on company_info c (cost=0.00..652645.60 rows=537460 width=288) (actual time=0.603..12000.314 rows=421602 loops=3)
-> Parallel Hash (cost=2209142.52..2209142.52 rows=5388152 width=1128) (actual time=87202.693..87202.694 rows=2040639 loops=3)
Buckets: 4096 Batches: 4096 Memory Usage: 2208kB
-> Parallel Seq Scan on tech_info t (cost=0.00..2209142.52 rows=5388152 width=1128) (actual time=1.921..50208.931 rows=2040639 loops=3)
Planning Time: 0.635 ms
Execution Time: 156547.780 ms
下面是创建的索引
CREATE INDEX IF NOT EXISTS domain_info_domain_id_idx
ON prospects_v6.domain_info USING btree
(domain_id ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS cmp_info_domain_id_idx
ON prospects_v6.company_info USING btree
(domain_id ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS tech_info_domain_id_idx
ON prospects_v6.tech_info USING btree
(domain_id ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS domain_info_prosp_id_idx
ON prospects_v6.domain_info USING btree
(prospector_id ASC NULLS LAST)
TABLESPACE pg_default;
有没有其他方法可以使查询更快？如果我关闭enable_hashjoin，它在数据库中会有什么影响。
推荐答案
对所有表运行Analyze已修复问题。附加执行计划
" Sort Key: c.cmp_web_traff_rank, c.cmp_domain"
" Sort Method: external merge Disk: 3072kB"
" -> Nested Loop Left Join (cost=1.84..80709.74 rows=2500 width=1811) (actual time=0.441..31.347 rows=2500 loops=1)"
" -> Nested Loop Left Join (cost=1.42..60405.24 rows=2500 width=1522) (actual time=0.427..23.790 rows=2500 loops=1)"
" -> Nested Loop Left Join (cost=0.99..39616.24 rows=2500 width=253) (actual time=0.413..14.473 rows=2500 loops=1)"
" -> Index Scan using prospector_id_idx on domain_info d (cost=0.56..20993.99 rows=2500 width=225) (actual time=0.398..9.105 rows=2500 loops=1)"
" Index Cond: (prospector_id = ANY ('{249774,13454209,58615,13325575,75967,19528,12957386,64166,12858867,13427659,65575,33491,30000,160996,73412,13247494,27474,77110,74547,194041,105678,110449,126367,129243,190780,112393,141770,142119,95529,12885584,297705,301822,151802,126853,12918591,13308031,127024,95934,195260,106300,37695,173275,392079,392382,13259977,223723,224588,13464314,236274,236852,406972,139709,33231,266948,279299,53708,6508,25713,14100957,14100996,13191251,13245996,31949,464538,325332,12955296,13296762,50790,341184,14103278,96793,17916,53929,38605,128025,20344,26948,495197,363975,117749,367345,368820,368988,369314,13238078,18780,27343,374906,376181,12954415,86504,377768,268383,379150,97261,12874057,268691,13287317,12907052,27779,23959,385723,50967,12953994,17660,391695,88279,394208,113307,396672,89801,49301,400183,13441946,30548,403900,405187,405776,406624,139127,14095873,13437007,418073,418155,5585,418879,419754,420670,421752,242982,135175,424940,37989,13468838,13432131,101635,46017,12952806,94249,7835,98447,12944904,647246,443863,155123,445651,446062,100234,100342,110735,93286,111124,132044,101134,460286,461070,112933,467275,470514,470780,113740,13639574,476954,481929,138655,13206487,114430,12830087,13193207,282941,13300276,13260416,13269390,497747,497847,12956698,190659,13443972,854661,859445,13533034,863131,503943,13444107,104447,12952629,672006,874248,139838,12871418,13222512,881352,510521,12939571,511669,889263,13615241,13060416,12870982,13431067,115048,135830,13615545,12947731,906573,12591683,13450197,520577,13477615,913034,12861274,12911285,13478070,522975,182549,177366,181998,210880,286104,203659,529113,1122756,12955650,13495947,211556,298509,217416,13557153,545414,172221,184801,138437,548186,548659,302957,550546,307285,193499,310556,561688,563009,165486,170987,1088527,568039,568465,568929,13219546,571500,572558,1331934,13432603,579244,329912,1159071,583877,14098766,214148,136433,332260,588257,13580713,592419,13225329,12899874,597889,343661,13584592,217896,1244063,347824,200700,1270969,615054,616582,617824,618925,359732,170533,626272,240609,12868230,12956434,1330868,13566527,632136,12958979,633371,12954011,13480629,13462685,1341028,12924989,13271743,637571,638263,1344447,14383919,12958224,1346925,13592665,13499671,12958637,1350364,1351518,12878460,1353168,1356134,648078,648771,1359645,14383779,12836580,651758,12955970,13500459,653490,12931375,14346449,1365263,14383753,657390,1367417,658023,13570969,12839669,659930,12893346,13509078,665142,12949763,1378581,12946903,13444736,12947948,1388859,13500406,13304648,13224378,13176369,678525,13177997,1398275,1398720,12834478,695091,695236,13240941,13428970,756263,756752,762011,1428878,764020,1431981,766094,766738,12864212,1438842,769567,1445376,13314057,13285900,1446551,13616440,776855,776924,778308,1453905,14383484,14370166,12938215,1465462,786908,1476949,13513292,790391,1482624,1487739,1499263,1500192,800116,1506408,803446,1511343,1512750,810621,1533052,813830,814873,1548040,1549268,12908547,13207579,827006,830698,832648,12885193,1587223,837585,13624210,840125,12883582,14383374,846445,1597966,13257758,14365777,849489,1599289,1601744,1603178,14159820,13062168,12929459,1611335,12954090,1619403,12957624,13288824,13288130,1638526,1646858,12921512,1657585,13474511,12905539,2297429,1685307,12913752,1702050,2578117,883618,886023,888177,13271262,889120,13470940,12813053,894404,13482679,13529902,13254604,908304,908632,913011,13456947,13455122,918727,13458762,922307,13459197,13447009,923976,925775,12844833,928725,929561,930005,13515373,932079,932817,13520823,13446104,13471973,13499555,940618,941312,13536842,943557,943699,944245,947756,13478143,948787,950834,13475315,4173018,954363,956781,959979,961149,13489951,962270,963379,12958667,965490,965576,967321,13481473,13477990,13531167,13460497,971474,974514,13482339,976995,12913863,13511899,13460223,13515970,3475227,13516985,13462274,680620,12884979,1772681,12898486,12923868,13251529,13247630,987714,989146,990033,2901334,998418,998484,13060867,999731,1001106,1002868,1004087,1005664,13188642,1008984,1009119,1009341,1011693,12957438,1015151,1015224,1017345,1018988,13460301,12890463,1025764,1028733,12911923,12934285,13617414,12954283,12954180,3535138,1040268,12953507,1042193,1042612,12902270,1046476,12953113,1047569,1047930,1048227,1048535,2500544,1052387,2507045,2507656,2514104,1054831,1055588,1056646,2542869,14346038,12947244,1059944,2570471,1060156,1060642,1060721,1061997,12921730,13306625,2594612,1062766,1066670,1067613,12917484,1069411,2671102,2676040,2687659,2690698,1076359,1077921,13236519,13935639,1079025,2745346,1080458,1080871,2765613,13436129,1087281,1087358,14098906,2843788,1090048,12954839,1092131,13227783,12879696,1095765,1095833,1095883,1096651,1096905,12945247,12818980,2869020,13242631,2871141,1100880,2871272,2871341,13431326,1101513,2871905,1102270,2872799,2873363,1103829,12953488,14353809,1105388,14353806,1107074,12954869,1108511,12959279,12908891,13254517,12953925,2883033,2884973,1115477,13641116,2887190,12935206,1118378,1119036,2889656,2890200,1121109,1123374,12898877,2900938,2902784,1125320,2909589,1126879,2934729,2948148,1130616,12883451,2976946,2998347,1136656,13206781,1137109,12953753,1138243,3028392,14365657,3048119,3061405,4928837,3072595,1145607,3089029,3097876,13438343,3108237,1151039,3162398,3168257,3169241,1156404,3239087,1163060,14333328,1164041,1165581,1168433,1169083,3320063,3320735,3324227,12853868,3343704,14330355,1174748,3372049,1176229,12958436,1178592,1179211,3410088,3450911,3457792,3469149,3476786,14034443,3483450,3487896,1187630,1188139,1189224,13624430,3541705,3542127,1191420,3556516,1192643,12896095,3601389,1197540,13240679,1204518,3662784,1206656,1207160,3690259,1207884,3695757,3699032,1209608,1211768,1214752,14180083,3771241,1220344,1220755,1220852,3801564,1222837,3825095,12922890,1224922,3837484,3855080,1227781,3880711,3886163,1230717,3899027,1232307,3902593,1232993,1234662,1235021,1236616,3942111,1236991,1237048,1237500,3967358,3979002,4002410,4017867,4028206,4046468,4057599,1248481,4071428,1250251,1251610,1251866,1252088,1255585,1255940,4147536,4151437,12947313,4157063,4160102,14022023,4208129,1265157,1265654,1266607,1449453,4241057,4247971,1269903,1271900,1272253,4281458,1273143,1859919,1862327,1273778,1275722,2537472,2577981,3234918,3253686,1277774,13443208,1277865,3933994,3939480,3948809,3953500,4595975,4601587,1281198,1282211,1282502,4379786,4381033,4386454,5397182,5404930,5405775,5407456,5407510,4397957,1288111,5496980,5497404,5497728,5497749,5497810,5497831,5497847,5497955,5497960,5498108,5498179,5498233,5499505,5499583,5499838,5500192,5500214,5500221,5500306,5500450,5500476,5500648,5500714,5500835,5501077,5501092,5501173,5501270,5501291,5501547,5501638,5501839,5501995,5502247,5502636,5502727,5504369,5533610,5548929,5551432,5553460,5556052,5558599,5559676,5570906,1289021,3272995,1289448,12926638,1290831,12953506,14161779,1293273,4468088,1296180,4495245,5613923,5613925,1299992,1300434,1300661,1301697,1369030,1384513,1302969,4543465,4544706,14096142,4567961,1307374,1308486,1309560,2166213,4614255,2574449,2578595,2881788,3248509,3250497,1312818,3255541,3257897,3267364,3921554,3921729,4581134,4588756,4599772,4619339,4622831,4635718,12876971,1315493,5230668,1315807,4662959,4678185,4692758,5390842,5393561,5399542,5399838,5404072,4712291,4725991,5496410,5496483,5496855,5497347,5497407,5497412,5497419,5497521,5497671,5497809,5497841,5497856,5497914,5497995,5498047,5498132,5499582,5499916,5499929,5500320,5500407,5500492,5500774,5500852,5501303,5501389,5501403,5501419,5501664,5501871,5501891,5501954,5502231,5502264,5502651,5502734,5526486,5528156,5531099,5547765,5559899,5572915,5575892,2549142,4754694,12828290,4787741,4792666,1326755,1327164,936837,5613884,4867267,4870169,4873244,1330547,4882082,5525997,1335908,1372978,1378861,1387613,1396839,4924866,1335432,1337834,2593169,2603796,2612963,2618476,2622728,4974711,2626020,4976567,3286376,3301176,4982756,3316346,3954509,3968501,3976509,3981891,3987746,4646161,4659686,1340885,5035730,5045470,5393472,5395166,5401711,5403175,5403355,5403506,5405896,12875565,5057862,5058603,5064371,5469576,5496642,5497334,5497388,5497478,5497501,5497759,5497764,5497788,5497849,5497887,5497954,5498051,5498256,5499573,5499578,5499618,5499703,5499736,5499875,5499900,5499949,5500052,5500074,5500184,5500185,5500259,5500292,5500345,5500388,5500575,5500744,5500931,5501100,5501144,5501206,5501229,5501385,5501499,5501670,5501702,5502323,5502389,5502546,5502595,5502700,5502705,5506778,5507274,5507589,5509896,5516686,5517891,5523796,5532819,5533390,5540950,5543228,5545866,5554605,5555022,5566430,5566512,5571496,5578225,5582225,5583881,5091504,5092577,14329873,13246792,522098,14094808,5132700,13436508,13259100,5151792,741162,5169080,1354385,5613885,5186990,1356282,1354210,1357655,1396163,12895247,1360347,12908300,13545092,12944740,2600060,13565843,2619306,2619843,3333182,3971836,3974469,3998393,5224940,4634111,4640855,4653325,4673376,5225920,5496766,13544524,5393244,5393476,5398570,5399687,5401111,5466014,5496674,5497459,5497551,5497818,5498077,5499613,5499746,5499860,5499925,5499928,5500028,5500241,5500406,5500484,5500511,5500603,5500647,5501070,5501278,5501485,5501575,5501646,5501881,5501917,5501982,5502015,5502125,5502189,5502275,5502388,5502454,5502455,5502565,5502719,5502729,13251580,5510131,5515019,5516541,5521065,5527149,5532588,5533117,5535047,5535400,5538718,5549287,5552699,5558562,5559116,5559649,5573268,5574283,5575075,5585342,5585747,1374643,12858811,5251654,12895556,5613929,12957154,1357335,1382841,12957204,1384360,1384770,1385386,1893120,2646338,3327169,3341982,3363847,3369998,4020079,4032510,4036328,4037402,4038482,4666906,4681505,4684347,4712222,4712386,5390390,5391723,5401467,5403484,5403739,12959073,1399002,5496277,5497137,5497387,5497520,5497908,5498005,5498090,5498168,5498252,5498258,5499599,5499819,5499913,5499951,5500309,5500310,5500315,5500422,5500423,5500478,5500548,5500642,5500670,5500962,5501123,5501239,5501532,5501582,5501800,5502039,5502284,5502485,5502506,5502598,5502649,5502650,5502663,5502717,5506428,5507340,5534388,5534476,5535543,5546045,5548947,5552291,5557606,5557866,5558584,5566467,5571630,5572730,5578704,1400780,12938837,1402905,12890521,12890948,1408760,12956996,12956794,13205341,13260479,12891302,1180380,12921939,1419459,1420502,1424909,1426484,1426659,13202001,13256920,1427026,5280723,1428129,13202551,13202443,5281540,12919351,13260757,12949979,12958841,5286785,1441250,1442223,1443597,13199859,13306791,1444812,367001,12916761,12916893,12937290,5291900,12955924,12845903,1449962,14353324,12886028,5513714,5296349,5518698,5298157,1459216,13195985,1370741,1397142,1461032,13248533,1464900,5303104,5303802,5304383,2136295,12955655,1472132,1472386,2504651,2510465,2514014,2525739,2526880,2528220,2541057,2543049,3210558,3225075,3240885,3243480,3883018,3885694,4543564,4550848,4557717,4567625,4583573,5308407,12934557,1479888,12880743,12958484,12955206,1480562,5496775,1481974,12881115,13190135,5311362,1484786,13297960,5312141,5312351,5396251,5398059,1486702,5405988,1487020,12839223,1488557,5314876,5496734,1491171,5496816,5496947,5497053,5497226,5497345,5497364,5497399,5497509,5497574,5497675,5497724,5497840,5499947,5500161,5500336,5500387,5500469,5500475,5500622,5500723,5500770,5500792,5501127,5501281,5501322,5501841,5501943,5502096,5502114,5502249,5502277,5502365,5502366,5502382,5502415,5502556,5520464,5527123,5532878,5532925,5545325,5550114,5552352,5556635,5556890,5562210,5575044,5578371,5316281,1493501,13350765,1494366,13294533,13241754,13351132,1498142,12958416,12910444,1501111,12954822,5576689,13186237,5322893,1505281,927273,1505531,1506390,1506869,5324593,5324907,12946832,1510893,1331130,5326077,1356186,1398680,12945545,1514651,13287457,1518101,1518425,1520556,12874150,1521466,1522133,1523185,1523352,1524523,12908145,2506990,2512621,1527155,12961580,3202208,3215661,3216114,3219384,3220833,3232124,3237597,3241243,3879572,3888499,3893463,3907721,4577933,13232013,4587175,12906231,13286009,1535256,1535401,12830574,12872754,12930593,13286594,1538032,12954146,1540529,12875049,5398793,5400624,5406518,13181765,5496695,5496997,5497471,5497479,5497563,5497566,5497583,5497606,5497714,5497716,5497837,5499576,5499579,5499634,5499650,5500085,5500298,5500340,5500383,5500517,5500615,5500796,5500806,5500823,5501021,5501039,5501146,5501210,5501366,5501434,5501470,5501885,5501906,5501970,5502209,5502241,5502283,5502446,5502582,5502737,5506328,5526829,5528294,5529692,5533691,5535944,5538829,5539981,5540079,5545600,5548020,5548182,5550326,5556007,5565754,5569460,5576491,5581269,13182204,1547625,13182901,1551136,1557492,1558662,12957765,12952890,12862473,12926047,12817771,12952805,5958818,2641477,2650270,2654849,2657115,3344015,13014004,3365703,3367989,3371620,12952730,1570296,4036744,4037156,4665256,13215287,12925362,1574158,1574455,1574802,1575182,12934084,5357672,1575937,12943244,12864038,5393668,5394608,5399988,5405302,13181016,13149639,6217688,13220752,5496497,5496650,5497377,5497380,5497431,5497437,5497567,5497704,5497867,5497897,5498054,5498100,5498200,5498204,5499525,5499751,5499852,5499858,5500026,5500121,5500269,5500344,5500593,5500713,5500756,5501323,5501432,5501491,5501512,5501583,5501604,5501615,5501801,5502063,5502076,5502102,5502239,5502597,5514335,5516731,5517390,5527904,5532418,5533431,5534118,5537838,5538206,5540811,5548264,5551028,5552627,5552908,5556802,5557699,5558176,5569123,5577891,5585333,13435046,13434822,13434731,5364055,12953231,13131943,12900949,13434082,12929213,12869235,5368104,12868948,5368261,1591395,684901,795268,12868630,5370552,1593299,12944265,1181412,12944256,13225925,1334457,1342010,1400312,1473893,13284397,1598420,12944864,1600994,2675615,2681231,2681240,2691883,2692684,2696817,2699335,2704951,3379279,3381468,3381929,3389895,4050211,4052448,4068042,4069785,4087237,4714468,4716201,4737419,4758454,5383276,5383839,5385551,13436377,13332198,1609012,5390812,5398877,5401629,13275348,1610306,1612473,5389028,1614264,1614419,5497123,5497360,5497659,5497721,5497770,5497872,5499686,5499933,5499991,5500144,5500153,5500175,5500187,5500231,5500319,5500342,5500488,5500499,5500752,5501404,5501493,5501544,5501658,5501815,5501987,5502127,5502273,5502433,5502510,5502523,5502531,5502618,5502635,5505358,5514783,5517745,5531655,5537264,5538927,5539481,5540810,5541115,5543019,5548157,5548329,5552288,5553510,5554209,5557027,5560190,5574735,5585552,1615499,12880108,1618641,1620177,1620832,12892628,13187968,13211237,1628187,1629156,1629316,12952721,7360693,5407518,13427673,1381592,14243001,1636702,1637490,7502044,5421821,5423530,1642699,1643502,1930678,13590723,13634917,1648120,13626487,2680620,2694271,2694933,2695926,2697930,2702714,3362991,3376600,3385314,3395464,1650584,3417642,3418412,4046073,4054320,4064077,4064789,4072593,4074103,4078029,4705357,4716056,4723960,4725513,4730598,1653419,13666523,1655693,1656558,13615641,1660648,5393808,5395717,5395868,5396081,5396974,5406517,5407218,13624905,1665337,1666328,5496479,5497487,5497593,5497667,5497727,5498188,5498249,5499508,5499562,5499627,5499850,5499923,5500076,5500227,5500251,5500436,5500443,5500580,5500638,5500697,5501170,5501353,5501374,5501462,5501483,5501560,5501726,5501835,5502024,5502044,5502051,5502232,5502381,5502460,5502462,5502474,5502575,5502628,5502712,5504333,5510884,5521346,5523137,5527250,5527807,5527869,5528570,5534156,5537660,5541698,5555458,5555478,5556035,5565148,5575706,3400709,4733057,1667909,1670233,1670560,1671606,1673758,13619962,1675460,1675933,1676354,1679299,13911907,1342235,1342606,1356006,1356861,1371834,1372432,1689414,13592768,13591571,1693140,1695202,1696485,1700798,1701691,13636165,2734960,2741565,3411583,3414209,3435245,3441038,3447685,4090281,4101416,4119058,4755369,4775782,4780879,4783075,4785411,4785616,4795085,5390723,5391177,5392455,5392690,5400604,5404572,5405962,5613920,9042027,5436984,5439284,1720126,1720475,5440956,5497134,5497417,5497526,5497610,5497789,5498044,5498240,5498269,5499558,5499592,5499609,5499614,5499664,5499714,5499823,5500039,5500155,5500284,5500445,5500715,5500952,5501004,5501022,5501037,5501041,5501058,5501098,5501539,5501571,5501832,5502309,5502477,5502502,5502614,5502713,5536233,5540634,5540673,5541498,5544451,5551117,5551304,5555049,5558417,5559164,5566653,5571694,5572073,5574145,5574472,5578183,13669616,1722798,1723231,1725899,13621133,1728931,1729988,13618234,1112169,13615558,1333270,1342991,1355371,13622328,13685616,13616710,1936343,13628087,13646403,2723223,2736467,13648529,3448118,3454396,3454564,4083057,4108793,4113628,4125374,4750444,4757000,4765865,4772334,4776361,4793988,5496755,1739338,5393001,5398778,5399768,5402206,5613899,5496785,5497562,5497701,5497845,5497868,5497963,5498018,5499584,5499824,5499917,5500369,5500414,5500453,5500578,5500778,5500867,5500901,5500922,5500978,5500989,5501084,5501478,5501821,5501859,5501942,5502018,5502202,5502505,5502631,5502653,5510982,5514479,5522840,5529512,5531785,5532262,5532352,5540875,5545916,5548755,5555655,5558515,5564487,5573065,5574486,5574492,5580255,1742156,783636,14020414,1332240,1341561,1344992,1386564,1387264,1527382,5501687,14022375,5502491,5505771,14098876,5508607,1745108,5510318,5513540,5514427,2752961,2767031,2773294,2787221,2787237,2788863,14099490,3475296,3478129,3494295,5521833,4164045,4173931,4802639,5523649,4822630,12857681,13355992,13080567,14115469,13414016,12848135,13362174,13201521,13445939,400405,12957131,446467,12998637,13034285,12994092,12909427,12994045,12875174,12874684,13154282,13094526,13175005,13151728,13334828,14132071,12848777,13046598,735163,764594,13320721,13212008,828463,13249584,13453154,13184726,12844485,14246754,13092287,13350305,12911218,13086692,13038246,930174,13201006,12988630,12970557,14105335,13079738,14105346,12987160,13055205,5392458,13079850,5394222,13158176,12989207,13083254,13051342,1240782,1241904,13247560,13012044,1351450,1355377,14026767,1371336,1384051,1385834,1395414,1395632,1397373,1398044,1398803,13130441,13052117,1418600,1419704,1421115,1424309,1428540,12928053,1453308,1470163,1472041,13253586,13244518,1515552,13113077,1536951,1549953,13191681,1589066,1593344,1622831,1624761,1643959,1661565,1666794,13008608,13252139,12929012,5496435,5496477,5497454,5497571,5497873,5497902,5498068,5498128,5498219,5499510,5499603,5499611,5499628,5499783,5500240,5500296,5500308,5500455,5500619,5500709,5500889,5501030,5501186,5501213,5501335,5501427,5501536,5501555,5501601,13462775,5501752,5501753,5501844,5501998,5502109,5502193,5502245,5502418,5502457,5502498,5502602,5502639,5502709,5502725,5509266,5509733,5528057,5531290,5535124,5539925,5540541,5544304,5547148,5553730,5554591,5558222,5558556,5571227,5572675,5576432,13051953,12841622,13077831,12847137,14102801,13425942,12989790,12864221,13133258,13204480,13185600,13000174,13211043,13182917,13199930,12820384,12820917,12873813,13027894,13026562,13066659,1843008,1843250,13033843,1844069,13247029,1844096,13001728,13248035,1845631,1845770,13151368,13222939,13050863,12940708,2463039,12909170,12931592,13068530,2481922,12824319,12824635,13148280,2501895,2501991,2502015,2502120,13266152,2503245,2503970,2504147,2504212,2504486,2506043,2506305,2506801,2507289,2507758,2507775,2508141,2508299,2508300,2508522,12975688,2508699,2509330,2509478,2509770,2509952,2510024,2510285,2510469,2510600,2510784,2510869,2511058,2511893,2511898,2512590,2514363,2514369,2514405,2514514,2516234,2516425,13034994,2516962,2517331,2517953,2518065,2518088,2520390,2520951,2521034,2521079,2521815,2521876,2522729,2523311,2523734,2524606,2525563,13027251,2526021,2527720,2527952,2527986,2528032,2529490,2529540,2529558,2529928,2530296,2530645,2530820,14023375,2531043,2531259,2531944,2531978,2532491,2532529,2532794,2533027,2533110,2533167,2533387,2533440,2533809,2534414,2534556,13330510,2535401,2535529,2536825,2536827,2537490,2537672,13382985,2538154,2538950,2539498,2540786,2541296,2541602,2542669,2542943,2544197,2872694,12865020,13109380,12971381,13088590,3867734,3867776,3867788,3868668,3869156,3869364,3869695,3870831,3876884,3877170,3877637,3877658,3877902,3878071,3878411,13254082,3878481,3879074,3879657,3880949,3882748,3882881,3884445,3884449,3884693,13009706,3885107,3885767,3887479,3888524,3888572,3888779}'::integer[]))"
" Filter: (domain_id IS NOT NULL)"
" -> Index Scan using m_all_tags_pkey on m_all_tags m (cost=0.42..7.45 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=2500)"
" Index Cond: (tag_id = d.tag_id)"
" -> Index Scan using tech_info_domain_id_key on tech_info t (cost=0.43..8.32 rows=1 width=1277) (actual time=0.003..0.003 rows=1 loops=2500)"
" Index Cond: (domain_id = d.domain_id)"
" -> Index Scan using cmp_info_p_key on company_info c (cost=0.43..8.12 rows=1 width=287) (actual time=0.002..0.002 rows=0 loops=2500)"
" Index Cond: (domain_id = d.domain_id)"
"Planning Time: 2.097 ms"
"Execution Time: 45.727 ms"```
这篇关于Postgres中的查询速度较慢，同时连接具有10M行的所有连接列索引的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
