By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table your_table(fixtures) as values
('{ "fixture_1":"fixture1"
,"fixture_2":"fixture2"
,"fixture_321":"fixture321"}');
select regexp_substr(json_object_keys(fixtures::json),'(\d+)$')::int as "ID"
from your_table;
SELECT 1
| ID |
|---|
| 1 |
| 2 |
| 321 |
SELECT 3
select setseed(.42);
alter table your_table alter column fixtures type jsonb using fixtures::jsonb;
insert into your_table
select jsonb_object_agg('fixture_'||n,'fixture'||n)
from generate_series(1,1e4)g,
lateral(select g as g1, (random()*1e3)::int n
from generate_series(1,(random()*100)::int))
group by g;
select * from your_table tablesample bernoulli(.42)repeatable(.42)limit 10;
| setseed |
|---|
SELECT 1
ALTER TABLE
INSERT 0 10000
| fixtures |
|---|
| {"fixture_4": "fixture4", "fixture_8": "fixture8", "fixture_17": "fixture17", "fixture_32": "fixture32", "fixture_34": "fixture34", "fixture_37": "fixture37", "fixture_41": "fixture41", "fixture_61": "fixture61", "fixture_71": "fixture71", "fixture_88": "fixture88", "fixture_135": "fixture135", "fixture_169": "fixture169", "fixture_195": "fixture195", "fixture_198": "fixture198", "fixture_204": "fixture204", "fixture_244": "fixture244", "fixture_265": "fixture265", "fixture_277": "fixture277", "fixture_281": "fixture281", "fixture_287": "fixture287", "fixture_291": "fixture291", "fixture_311": "fixture311", "fixture_317": "fixture317", "fixture_320": "fixture320", "fixture_324": "fixture324", "fixture_328": "fixture328", "fixture_334": "fixture334", "fixture_342": "fixture342", "fixture_364": "fixture364", "fixture_378": "fixture378", "fixture_381": "fixture381", "fixture_396": "fixture396", "fixture_425": "fixture425", "fixture_462": "fixture462", "fixture_468": "fixture468", "fixture_472": "fixture472", "fixture_480": "fixture480", "fixture_485": "fixture485", "fixture_494": "fixture494", "fixture_519": "fixture519", "fixture_525": "fixture525", "fixture_532": "fixture532", "fixture_537": "fixture537", "fixture_549": "fixture549", "fixture_566": "fixture566", "fixture_592": "fixture592", "fixture_596": "fixture596", "fixture_602": "fixture602", "fixture_622": "fixture622", "fixture_627": "fixture627", "fixture_678": "fixture678", "fixture_713": "fixture713", "fixture_767": "fixture767", "fixture_777": "fixture777", "fixture_785": "fixture785", "fixture_796": "fixture796", "fixture_808": "fixture808", "fixture_812": "fixture812", "fixture_824": "fixture824", "fixture_836": "fixture836", "fixture_847": "fixture847", "fixture_861": "fixture861", "fixture_875": "fixture875", "fixture_881": "fixture881", "fixture_901": "fixture901", "fixture_910": "fixture910", "fixture_916": "fixture916", "fixture_920": "fixture920", "fixture_927": "fixture927", "fixture_952": "fixture952", "fixture_984": "fixture984"} |
| {"fixture_3": "fixture3", "fixture_4": "fixture4", "fixture_56": "fixture56", "fixture_59": "fixture59", "fixture_99": "fixture99", "fixture_102": "fixture102", "fixture_108": "fixture108", "fixture_131": "fixture131", "fixture_132": "fixture132", "fixture_143": "fixture143", "fixture_153": "fixture153", "fixture_162": "fixture162", "fixture_173": "fixture173", "fixture_175": "fixture175", "fixture_184": "fixture184", "fixture_189": "fixture189", "fixture_199": "fixture199", "fixture_205": "fixture205", "fixture_235": "fixture235", "fixture_244": "fixture244", "fixture_256": "fixture256", "fixture_266": "fixture266", "fixture_300": "fixture300", "fixture_305": "fixture305", "fixture_352": "fixture352", "fixture_353": "fixture353", "fixture_355": "fixture355", "fixture_359": "fixture359", "fixture_389": "fixture389", "fixture_402": "fixture402", "fixture_410": "fixture410", "fixture_411": "fixture411", "fixture_462": "fixture462", "fixture_477": "fixture477", "fixture_486": "fixture486", "fixture_537": "fixture537", "fixture_547": "fixture547", "fixture_554": "fixture554", "fixture_564": "fixture564", "fixture_581": "fixture581", "fixture_584": "fixture584", "fixture_607": "fixture607", "fixture_629": "fixture629", "fixture_637": "fixture637", "fixture_642": "fixture642", "fixture_650": "fixture650", "fixture_669": "fixture669", "fixture_685": "fixture685", "fixture_688": "fixture688", "fixture_708": "fixture708", "fixture_730": "fixture730", "fixture_744": "fixture744", "fixture_765": "fixture765", "fixture_799": "fixture799", "fixture_804": "fixture804", "fixture_817": "fixture817", "fixture_826": "fixture826", "fixture_848": "fixture848", "fixture_860": "fixture860", "fixture_862": "fixture862", "fixture_873": "fixture873", "fixture_877": "fixture877", "fixture_881": "fixture881", "fixture_899": "fixture899", "fixture_914": "fixture914", "fixture_915": "fixture915", "fixture_931": "fixture931", "fixture_950": "fixture950", "fixture_953": "fixture953", "fixture_968": "fixture968", "fixture_978": "fixture978", "fixture_982": "fixture982"} |
| {"fixture_1": "fixture1", "fixture_8": "fixture8", "fixture_23": "fixture23", "fixture_37": "fixture37", "fixture_41": "fixture41", "fixture_49": "fixture49", "fixture_57": "fixture57", "fixture_129": "fixture129", "fixture_154": "fixture154", "fixture_172": "fixture172", "fixture_175": "fixture175", "fixture_208": "fixture208", "fixture_257": "fixture257", "fixture_258": "fixture258", "fixture_260": "fixture260", "fixture_264": "fixture264", "fixture_265": "fixture265", "fixture_283": "fixture283", "fixture_301": "fixture301", "fixture_307": "fixture307", "fixture_312": "fixture312", "fixture_347": "fixture347", "fixture_382": "fixture382", "fixture_387": "fixture387", "fixture_407": "fixture407", "fixture_411": "fixture411", "fixture_412": "fixture412", "fixture_449": "fixture449", "fixture_468": "fixture468", "fixture_471": "fixture471", "fixture_496": "fixture496", "fixture_504": "fixture504", "fixture_511": "fixture511", "fixture_517": "fixture517", "fixture_534": "fixture534", "fixture_536": "fixture536", "fixture_593": "fixture593", "fixture_600": "fixture600", "fixture_605": "fixture605", "fixture_607": "fixture607", "fixture_612": "fixture612", "fixture_635": "fixture635", "fixture_685": "fixture685", "fixture_711": "fixture711", "fixture_712": "fixture712", "fixture_728": "fixture728", "fixture_744": "fixture744", "fixture_747": "fixture747", "fixture_752": "fixture752", "fixture_760": "fixture760", "fixture_762": "fixture762", "fixture_774": "fixture774", "fixture_783": "fixture783", "fixture_799": "fixture799", "fixture_801": "fixture801", "fixture_827": "fixture827", "fixture_833": "fixture833", "fixture_835": "fixture835", "fixture_840": "fixture840", "fixture_852": "fixture852", "fixture_861": "fixture861", "fixture_894": "fixture894", "fixture_899": "fixture899", "fixture_900": "fixture900", "fixture_901": "fixture901", "fixture_937": "fixture937", "fixture_943": "fixture943", "fixture_948": "fixture948", "fixture_949": "fixture949", "fixture_954": "fixture954", "fixture_967": "fixture967", "fixture_997": "fixture997"} |
| {"fixture_0": "fixture0", "fixture_3": "fixture3", "fixture_14": "fixture14", "fixture_88": "fixture88", "fixture_101": "fixture101", "fixture_109": "fixture109", "fixture_125": "fixture125", "fixture_128": "fixture128", "fixture_151": "fixture151", "fixture_153": "fixture153", "fixture_179": "fixture179", "fixture_186": "fixture186", "fixture_197": "fixture197", "fixture_204": "fixture204", "fixture_237": "fixture237", "fixture_240": "fixture240", "fixture_245": "fixture245", "fixture_259": "fixture259", "fixture_266": "fixture266", "fixture_272": "fixture272", "fixture_291": "fixture291", "fixture_344": "fixture344", "fixture_362": "fixture362", "fixture_379": "fixture379", "fixture_381": "fixture381", "fixture_383": "fixture383", "fixture_397": "fixture397", "fixture_398": "fixture398", "fixture_405": "fixture405", "fixture_406": "fixture406", "fixture_417": "fixture417", "fixture_499": "fixture499", "fixture_537": "fixture537", "fixture_544": "fixture544", "fixture_550": "fixture550", "fixture_555": "fixture555", "fixture_577": "fixture577", "fixture_598": "fixture598", "fixture_599": "fixture599", "fixture_602": "fixture602", "fixture_605": "fixture605", "fixture_607": "fixture607", "fixture_614": "fixture614", "fixture_626": "fixture626", "fixture_633": "fixture633", "fixture_666": "fixture666", "fixture_682": "fixture682", "fixture_706": "fixture706", "fixture_707": "fixture707", "fixture_720": "fixture720", "fixture_722": "fixture722", "fixture_724": "fixture724", "fixture_754": "fixture754", "fixture_762": "fixture762", "fixture_763": "fixture763", "fixture_767": "fixture767", "fixture_784": "fixture784", "fixture_788": "fixture788", "fixture_790": "fixture790", "fixture_812": "fixture812", "fixture_823": "fixture823", "fixture_842": "fixture842", "fixture_857": "fixture857", "fixture_870": "fixture870", "fixture_872": "fixture872", "fixture_882": "fixture882", "fixture_897": "fixture897", "fixture_916": "fixture916", "fixture_941": "fixture941", "fixture_966": "fixture966", "fixture_994": "fixture994"} |
| {"fixture_26": "fixture26", "fixture_72": "fixture72", "fixture_94": "fixture94", "fixture_105": "fixture105", "fixture_124": "fixture124", "fixture_132": "fixture132", "fixture_138": "fixture138", "fixture_146": "fixture146", "fixture_155": "fixture155", "fixture_156": "fixture156", "fixture_163": "fixture163", "fixture_191": "fixture191", "fixture_206": "fixture206", "fixture_240": "fixture240", "fixture_251": "fixture251", "fixture_267": "fixture267", "fixture_272": "fixture272", "fixture_297": "fixture297", "fixture_299": "fixture299", "fixture_303": "fixture303", "fixture_316": "fixture316", "fixture_326": "fixture326", "fixture_334": "fixture334", "fixture_344": "fixture344", "fixture_352": "fixture352", "fixture_371": "fixture371", "fixture_386": "fixture386", "fixture_409": "fixture409", "fixture_411": "fixture411", "fixture_419": "fixture419", "fixture_420": "fixture420", "fixture_421": "fixture421", "fixture_426": "fixture426", "fixture_469": "fixture469", "fixture_472": "fixture472", "fixture_503": "fixture503", "fixture_510": "fixture510", "fixture_518": "fixture518", "fixture_531": "fixture531", "fixture_557": "fixture557", "fixture_566": "fixture566", "fixture_583": "fixture583", "fixture_584": "fixture584", "fixture_593": "fixture593", "fixture_601": "fixture601", "fixture_619": "fixture619", "fixture_620": "fixture620", "fixture_622": "fixture622", "fixture_640": "fixture640", "fixture_675": "fixture675", "fixture_702": "fixture702", "fixture_707": "fixture707", "fixture_714": "fixture714", "fixture_717": "fixture717", "fixture_729": "fixture729", "fixture_739": "fixture739", "fixture_760": "fixture760", "fixture_792": "fixture792", "fixture_793": "fixture793", "fixture_824": "fixture824", "fixture_828": "fixture828", "fixture_849": "fixture849", "fixture_850": "fixture850", "fixture_856": "fixture856", "fixture_882": "fixture882", "fixture_893": "fixture893", "fixture_896": "fixture896", "fixture_905": "fixture905", "fixture_921": "fixture921", "fixture_942": "fixture942", "fixture_947": "fixture947", "fixture_960": "fixture960"} |
| {"fixture_51": "fixture51", "fixture_56": "fixture56", "fixture_85": "fixture85", "fixture_94": "fixture94", "fixture_98": "fixture98", "fixture_104": "fixture104", "fixture_107": "fixture107", "fixture_129": "fixture129", "fixture_155": "fixture155", "fixture_168": "fixture168", "fixture_171": "fixture171", "fixture_174": "fixture174", "fixture_203": "fixture203", "fixture_224": "fixture224", "fixture_244": "fixture244", "fixture_249": "fixture249", "fixture_260": "fixture260", "fixture_273": "fixture273", "fixture_285": "fixture285", "fixture_288": "fixture288", "fixture_298": "fixture298", "fixture_310": "fixture310", "fixture_332": "fixture332", "fixture_336": "fixture336", "fixture_343": "fixture343", "fixture_346": "fixture346", "fixture_379": "fixture379", "fixture_386": "fixture386", "fixture_401": "fixture401", "fixture_404": "fixture404", "fixture_407": "fixture407", "fixture_411": "fixture411", "fixture_424": "fixture424", "fixture_428": "fixture428", "fixture_454": "fixture454", "fixture_456": "fixture456", "fixture_461": "fixture461", "fixture_465": "fixture465", "fixture_484": "fixture484", "fixture_498": "fixture498", "fixture_504": "fixture504", "fixture_520": "fixture520", "fixture_527": "fixture527", "fixture_534": "fixture534", "fixture_538": "fixture538", "fixture_546": "fixture546", "fixture_547": "fixture547", "fixture_551": "fixture551", "fixture_587": "fixture587", "fixture_608": "fixture608", "fixture_619": "fixture619", "fixture_621": "fixture621", "fixture_641": "fixture641", "fixture_645": "fixture645", "fixture_656": "fixture656", "fixture_678": "fixture678", "fixture_689": "fixture689", "fixture_700": "fixture700", "fixture_714": "fixture714", "fixture_767": "fixture767", "fixture_810": "fixture810", "fixture_836": "fixture836", "fixture_843": "fixture843", "fixture_861": "fixture861", "fixture_865": "fixture865", "fixture_882": "fixture882", "fixture_911": "fixture911", "fixture_930": "fixture930", "fixture_931": "fixture931", "fixture_946": "fixture946", "fixture_947": "fixture947", "fixture_963": "fixture963"} |
| {"fixture_33": "fixture33", "fixture_35": "fixture35", "fixture_42": "fixture42", "fixture_57": "fixture57", "fixture_71": "fixture71", "fixture_73": "fixture73", "fixture_77": "fixture77", "fixture_78": "fixture78", "fixture_83": "fixture83", "fixture_115": "fixture115", "fixture_118": "fixture118", "fixture_145": "fixture145", "fixture_148": "fixture148", "fixture_162": "fixture162", "fixture_167": "fixture167", "fixture_169": "fixture169", "fixture_179": "fixture179", "fixture_187": "fixture187", "fixture_203": "fixture203", "fixture_207": "fixture207", "fixture_249": "fixture249", "fixture_254": "fixture254", "fixture_261": "fixture261", "fixture_265": "fixture265", "fixture_267": "fixture267", "fixture_282": "fixture282", "fixture_300": "fixture300", "fixture_330": "fixture330", "fixture_345": "fixture345", "fixture_381": "fixture381", "fixture_382": "fixture382", "fixture_403": "fixture403", "fixture_404": "fixture404", "fixture_412": "fixture412", "fixture_457": "fixture457", "fixture_471": "fixture471", "fixture_504": "fixture504", "fixture_512": "fixture512", "fixture_529": "fixture529", "fixture_536": "fixture536", "fixture_538": "fixture538", "fixture_584": "fixture584", "fixture_585": "fixture585", "fixture_597": "fixture597", "fixture_609": "fixture609", "fixture_645": "fixture645", "fixture_656": "fixture656", "fixture_666": "fixture666", "fixture_670": "fixture670", "fixture_678": "fixture678", "fixture_683": "fixture683", "fixture_722": "fixture722", "fixture_724": "fixture724", "fixture_731": "fixture731", "fixture_736": "fixture736", "fixture_756": "fixture756", "fixture_757": "fixture757", "fixture_776": "fixture776", "fixture_804": "fixture804", "fixture_808": "fixture808", "fixture_811": "fixture811", "fixture_827": "fixture827", "fixture_832": "fixture832", "fixture_834": "fixture834", "fixture_854": "fixture854", "fixture_878": "fixture878", "fixture_894": "fixture894", "fixture_901": "fixture901", "fixture_917": "fixture917", "fixture_938": "fixture938", "fixture_945": "fixture945", "fixture_948": "fixture948", "fixture_952": "fixture952"} |
| {"fixture_23": "fixture23", "fixture_34": "fixture34", "fixture_41": "fixture41", "fixture_100": "fixture100", "fixture_105": "fixture105", "fixture_119": "fixture119", "fixture_124": "fixture124", "fixture_130": "fixture130", "fixture_132": "fixture132", "fixture_145": "fixture145", "fixture_149": "fixture149", "fixture_177": "fixture177", "fixture_186": "fixture186", "fixture_193": "fixture193", "fixture_195": "fixture195", "fixture_199": "fixture199", "fixture_210": "fixture210", "fixture_234": "fixture234", "fixture_258": "fixture258", "fixture_286": "fixture286", "fixture_298": "fixture298", "fixture_321": "fixture321", "fixture_326": "fixture326", "fixture_329": "fixture329", "fixture_365": "fixture365", "fixture_389": "fixture389", "fixture_445": "fixture445", "fixture_450": "fixture450", "fixture_455": "fixture455", "fixture_492": "fixture492", "fixture_511": "fixture511", "fixture_523": "fixture523", "fixture_525": "fixture525", "fixture_534": "fixture534", "fixture_554": "fixture554", "fixture_564": "fixture564", "fixture_572": "fixture572", "fixture_592": "fixture592", "fixture_594": "fixture594", "fixture_604": "fixture604", "fixture_612": "fixture612", "fixture_615": "fixture615", "fixture_616": "fixture616", "fixture_630": "fixture630", "fixture_657": "fixture657", "fixture_703": "fixture703", "fixture_724": "fixture724", "fixture_725": "fixture725", "fixture_729": "fixture729", "fixture_773": "fixture773", "fixture_791": "fixture791", "fixture_805": "fixture805", "fixture_813": "fixture813", "fixture_846": "fixture846", "fixture_853": "fixture853", "fixture_860": "fixture860", "fixture_862": "fixture862", "fixture_880": "fixture880", "fixture_882": "fixture882", "fixture_891": "fixture891", "fixture_899": "fixture899", "fixture_908": "fixture908", "fixture_916": "fixture916", "fixture_937": "fixture937", "fixture_966": "fixture966", "fixture_972": "fixture972", "fixture_974": "fixture974", "fixture_977": "fixture977", "fixture_987": "fixture987", "fixture_995": "fixture995", "fixture_996": "fixture996"} |
| {"fixture_1": "fixture1", "fixture_12": "fixture12", "fixture_17": "fixture17", "fixture_19": "fixture19", "fixture_35": "fixture35", "fixture_44": "fixture44", "fixture_46": "fixture46", "fixture_75": "fixture75", "fixture_81": "fixture81", "fixture_88": "fixture88", "fixture_90": "fixture90", "fixture_102": "fixture102", "fixture_137": "fixture137", "fixture_141": "fixture141", "fixture_173": "fixture173", "fixture_188": "fixture188", "fixture_197": "fixture197", "fixture_210": "fixture210", "fixture_221": "fixture221", "fixture_229": "fixture229", "fixture_239": "fixture239", "fixture_252": "fixture252", "fixture_279": "fixture279", "fixture_306": "fixture306", "fixture_311": "fixture311", "fixture_313": "fixture313", "fixture_314": "fixture314", "fixture_321": "fixture321", "fixture_324": "fixture324", "fixture_352": "fixture352", "fixture_381": "fixture381", "fixture_425": "fixture425", "fixture_427": "fixture427", "fixture_478": "fixture478", "fixture_519": "fixture519", "fixture_534": "fixture534", "fixture_543": "fixture543", "fixture_550": "fixture550", "fixture_577": "fixture577", "fixture_582": "fixture582", "fixture_589": "fixture589", "fixture_600": "fixture600", "fixture_612": "fixture612", "fixture_664": "fixture664", "fixture_690": "fixture690", "fixture_691": "fixture691", "fixture_694": "fixture694", "fixture_704": "fixture704", "fixture_723": "fixture723", "fixture_741": "fixture741", "fixture_750": "fixture750", "fixture_756": "fixture756", "fixture_770": "fixture770", "fixture_774": "fixture774", "fixture_781": "fixture781", "fixture_795": "fixture795", "fixture_800": "fixture800", "fixture_814": "fixture814", "fixture_821": "fixture821", "fixture_860": "fixture860", "fixture_862": "fixture862", "fixture_868": "fixture868", "fixture_872": "fixture872", "fixture_883": "fixture883", "fixture_891": "fixture891", "fixture_900": "fixture900", "fixture_907": "fixture907", "fixture_908": "fixture908", "fixture_919": "fixture919", "fixture_935": "fixture935", "fixture_938": "fixture938", "fixture_962": "fixture962", "fixture_980": "fixture980", "fixture_983": "fixture983"} |
| {"fixture_69": "fixture69", "fixture_70": "fixture70", "fixture_72": "fixture72", "fixture_106": "fixture106", "fixture_108": "fixture108", "fixture_111": "fixture111", "fixture_117": "fixture117", "fixture_120": "fixture120", "fixture_130": "fixture130", "fixture_133": "fixture133", "fixture_136": "fixture136", "fixture_151": "fixture151", "fixture_168": "fixture168", "fixture_181": "fixture181", "fixture_244": "fixture244", "fixture_257": "fixture257", "fixture_262": "fixture262", "fixture_266": "fixture266", "fixture_284": "fixture284", "fixture_319": "fixture319", "fixture_394": "fixture394", "fixture_398": "fixture398", "fixture_405": "fixture405", "fixture_427": "fixture427", "fixture_445": "fixture445", "fixture_449": "fixture449", "fixture_453": "fixture453", "fixture_459": "fixture459", "fixture_477": "fixture477", "fixture_479": "fixture479", "fixture_492": "fixture492", "fixture_496": "fixture496", "fixture_507": "fixture507", "fixture_531": "fixture531", "fixture_538": "fixture538", "fixture_552": "fixture552", "fixture_559": "fixture559", "fixture_564": "fixture564", "fixture_589": "fixture589", "fixture_590": "fixture590", "fixture_591": "fixture591", "fixture_605": "fixture605", "fixture_665": "fixture665", "fixture_671": "fixture671", "fixture_684": "fixture684", "fixture_701": "fixture701", "fixture_702": "fixture702", "fixture_705": "fixture705", "fixture_730": "fixture730", "fixture_742": "fixture742", "fixture_751": "fixture751", "fixture_756": "fixture756", "fixture_765": "fixture765", "fixture_768": "fixture768", "fixture_779": "fixture779", "fixture_787": "fixture787", "fixture_795": "fixture795", "fixture_806": "fixture806", "fixture_819": "fixture819", "fixture_829": "fixture829", "fixture_840": "fixture840", "fixture_849": "fixture849", "fixture_854": "fixture854", "fixture_855": "fixture855", "fixture_864": "fixture864", "fixture_894": "fixture894", "fixture_906": "fixture906", "fixture_915": "fixture915", "fixture_925": "fixture925", "fixture_935": "fixture935", "fixture_954": "fixture954"} |
SELECT 10
vacuum analyze your_table;
VACUUM
explain analyze verbose
select regexp_substr(jsonb_object_keys(fixtures),'(\d+)$')::int as "ID"
from your_table;
| QUERY PLAN |
|---|
| Result (cost=0.00..26087.52 rows=1000100 width=4) (actual time=0.036..1741.165 rows=713505 loops=1) |
| Output: (regexp_substr((jsonb_object_keys(fixtures)), '(\d+)$'::text))::integer |
| -> ProjectSet (cost=0.00..6085.52 rows=1000100 width=32) (actual time=0.018..255.615 rows=713505 loops=1) |
| Output: jsonb_object_keys(fixtures) |
| -> Seq Scan on public.your_table (cost=0.00..1010.01 rows=10001 width=684) (actual time=0.006..6.308 rows=10001 loops=1) |
| Output: fixtures |
| Planning Time: 0.075 ms |
| Execution Time: 1793.334 ms |
EXPLAIN
explain analyze verbose
select split_part(jsonb_object_keys(fixtures), '_', -1)::int as "ID"
from your_table;
| QUERY PLAN |
|---|
| Result (cost=0.00..26087.52 rows=1000100 width=4) (actual time=0.027..529.571 rows=713505 loops=1) |
| Output: (split_part((jsonb_object_keys(fixtures)), '_'::text, '-1'::integer))::integer |
| -> ProjectSet (cost=0.00..6085.52 rows=1000100 width=32) (actual time=0.010..237.609 rows=713505 loops=1) |
| Output: jsonb_object_keys(fixtures) |
| -> Seq Scan on public.your_table (cost=0.00..1010.01 rows=10001 width=684) (actual time=0.006..3.067 rows=10001 loops=1) |
| Output: fixtures |
| Planning Time: 0.071 ms |
| Execution Time: 571.439 ms |
EXPLAIN
explain analyze verbose
select regexp_substr(jsonb_object_keys(fixtures),'(\d+)$')::int as "ID"
from your_table;
| QUERY PLAN |
|---|
| Result (cost=0.00..26087.52 rows=1000100 width=4) (actual time=0.021..1451.372 rows=713505 loops=1) |
| Output: (regexp_substr((jsonb_object_keys(fixtures)), '(\d+)$'::text))::integer |
| -> ProjectSet (cost=0.00..6085.52 rows=1000100 width=32) (actual time=0.009..236.706 rows=713505 loops=1) |
| Output: jsonb_object_keys(fixtures) |
| -> Seq Scan on public.your_table (cost=0.00..1010.01 rows=10001 width=684) (actual time=0.005..3.087 rows=10001 loops=1) |
| Output: fixtures |
| Planning Time: 0.052 ms |
| Execution Time: 1495.210 ms |
EXPLAIN