Skip to content

Instantly share code, notes, and snippets.

View ereshzealous's full-sized avatar

Eresh ereshzealous

42 followers · 1 following
View GitHub Profile
@ereshzealous
ereshzealous / schema.json
Created September 7, 2021 10:00
{
"$schema": "https://json-schema.org/draft/2019-09/schema#",
"$id": "http://my-paintings-api.com/schemas/painting-schema.json",
"type": "object",
"required" : [
"profession", "gender", "maritalStatus", "dateOfBirth", "fatherName"
],
"properties" : {
"profession" : {
"type" : "string",
@ereshzealous
ereshzealous / query14.sql
Created September 7, 2021 09:29
explain analyze select * from user_details where details @> '{"info" : {"crop" : "Corn", "farmSize": "2-3"}}' limit 50;
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------|
Limit (cost=0.00..3501.85 rows=50 width=426) (actual time=0.016..0.587 rows=50 loops=1) |
-> Seq Scan on user_details (cost=0.00..262639.00 rows=3750 width=426) (actual time=0.015..0.581 rows=50 loops=1)|
Filter: (details @> '{"info": {"crop": "Corn", "farmSize": "2-3"}}'::jsonb) |
Rows Removed by Filter: 1046 |
Planning Time: 0.058 ms |
Execution Time: 0.613 ms
@ereshzealous
ereshzealous / query13.sql
Created September 7, 2021 09:28
explain analyze select * from user_details where details -> 'info' ->> 'crop' = 'Corn' and details -> 'info' ->> 'farmSize' = '2-3' limit 50;
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------------------------------------|
Limit (cost=1000.00..136551.01 rows=50 width=426) (actual time=3.764..4.346 rows=50 loops=1) |
-> Gather (cost=1000.00..255835.90 rows=94 width=426) (actual time=0.165..40.441 rows=50 loops=1) |
Workers Planned: 2 |
Workers Launched: 2
@ereshzealous
ereshzealous / query12.sql
Created September 7, 2021 09:27
explain analyze select * from user_details where details -> 'info' ->> 'crop' = 'Corn' limit 50;
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------|
Limit (cost=0.00..750.37 rows=50 width=426) (actual time=0.015..0.318 rows=50 loops=1) |
-> Seq Scan on user_details (cost=0.00..281389.00 rows=18750 width=426) (actual time=0.014..0.315 rows=50 loops=1)|
Filter: (((details -> 'info'::text) ->> 'crop'::text) = 'Corn'::text) |
Rows Removed by Filter: 210 |
Planning Time: 0.052 ms |
Execution Time: 0.335 ms
@ereshzealous
ereshzealous / query11.sql
Created September 7, 2021 09:26
explain analyze select * from user_details where to_date(details ->>'dateOfBirth', 'YYYY-MM-DD') between '2000-03-01' and '2000-03-31' limit 50;
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Limit (cost=0.00..825.37 rows=50 width=426) (actual time=0.113..27.525 rows=50 loops=1) |
-> Seq Scan on user_details (cost=0.00..309514.00 rows=18750 width=426) (actual time=0.112..27.515 rows=50 loops=1) |
Filter: ((to_date((details ->> 'dateOfBirth'::text), 'YYYY-MM-DD'::text) >= '2000-0
@ereshzealous
ereshzealous / query10.sql
Created September 7, 2021 09:25
explain analyze select * from user_details where to_date(details ->>'dateOfBirth', 'YYYY-MM-DD') = '2000-03-01' limit 50;
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------|
Limit (cost=0.00..750.37 rows=50 width=426) (actual time=0.148..1231.418 rows=50 loops=1) |
-> Seq Scan on user_details (cost=0.00..281389.00 rows=18750 width=426) (actual time=0.147..1231.401 rows=50 loops=1)|
Filter: (to_date((details ->> 'dateOfBirth'::text), 'YYYY-MM-DD'::text) = '2000-03-01'::date) |
Rows Removed by Filter: 2123988 |
Planning Time: 0.063 ms |
Execution Time: 1231.454 ms
@ereshzealous
ereshzealous / query9.sql
Created September 7, 2021 09:24
explain analyze select * from user_details where details ->> 'alternateContacts' like '%777%' limit 50;
QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------------|
Limit (cost=0.00..90.67 rows=50 width=426) (actual time=0.594..4.489 rows=50 loops=1) |
-> Seq Scan on user_details (cost=0.00..272014.00 rows=150000 width=426) (actual time=0.592..4.480 rows=50 loops=1)|
Filter: ((details ->> 'alternateContacts'::text) ~~ '%777%'::text) |
Rows Removed by Filter: 4384 |
Planning Time: 0.074 ms |
Execution Time: 4.510 ms
@ereshzealous
ereshzealous / query8.sql
Created September 7, 2021 09:23
explain analyze select * from user_details where details @> '{"alternateContacts" : ["+6171836370278"]}';
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------|
Gather (cost=1000.00..236670.25 rows=3750 width=426) (actual time=646.851..652.494 rows=1 loops=1) |
Workers Planned: 2 |
Workers Launched: 2 |
-> Parallel Seq Scan on user_details (cost=0.00..235295.25 rows=1562 width=426) (actual time=618.252..618.545 rows=0 loops=3)|
Filter: (details @> '{"alternateContacts": ["+6171836370278"]}'::jsonb) |
@ereshzealous
ereshzealous / query7.sql
Created September 7, 2021 09:22
explain analyze select * from user_details where details ->> 'alternateContacts' like '%+6171836370278%';
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------|
Gather (cost=1000.00..236670.25 rows=3750 width=426) (actual time=633.497..639.577 rows=1 loops=1) |
Workers Planned: 2 |
Workers Launched: 2 |
-> Parallel Seq Scan on user_details (cost=0.00..235295.25 rows=1562 width=426) (actual time=619.500..619.760 rows=0 loops=3)|
Filter: (details @> '{"alternateContacts": ["+6171836370278"]}'::jsonb) |
@ereshzealous
ereshzealous / query6.sql
Created September 7, 2021 09:21
explain analyze select * from user_details where details ->> 'alternateContacts' = '+6171836370278';
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------|
Gather (cost=1000.00..242076.50 rows=18750 width=426) (actual time=667.376..672.194 rows=0 loops=1) |
Workers Planned: 2 |
Workers Launched: 2 |
-> Parallel Seq Scan on user_details (cost=0.00..239201.50 rows=7812 width=426) (actual time=631.365..631.365 rows=0 loops=3)|
Filter: ((details ->> 'alternateContacts'::text) = '%+6171836370278%'::text) |