(((ig -> 'last_post_location'::text) -> 'locality_id'::text))ĬREATE TRIGGER influencers_search_vector_updateĮXECUTE PROCEDURE tsvector_update_trigger(s, e, a, r, c, h, _, v, e, c, t, o, r, \, 0, 0, 0, p, g, _, c, a, t, a, l, o, g. I am trying to speed up the querying of some json data stored inside a PostgreSQL database. (((ig -> 'last_post_location'::text) -> 'country_id'::text))ĬREATE INDEX ig_last_post_location_locality (((ig -> 'last_post_location'::text) -> 'administrative_area_level_3_id'::text))ĬREATE INDEX ig_last_post_location_country (((ig -> 'last_post_location'::text) -> 'administrative_area_level_2_id'::text)) However when I instead select a top-level path of the same jsonb column, the query slows down by 6x (3241ms). When I SELECT the entire jsonb column, the query is fast (574 ms). (((ig -> 'last_post_location'::text) -> 'administrative_area_level_1_id'::text)) I have a single SELECT that queries both regular postgres tables and a jsonb column. ((ig -> 'id'::text) COLLATE pg_catalog."default") Ig_updated_at timestamp without time zone,ĬONSTRAINT influencers_pkey PRIMARY KEY (id),ĬONSTRAINT ig_id_must_exist CHECK (ig ? 'id'::text),ĬONSTRAINT ig_username_must_exist CHECK (ig ? 'username'::text)Īnd the indexes created (relevant index first - the one I think it should be used): CREATE INDEX idx_btree_ig_id (Well, about as much as you can expect for a new data type in an RDBMS.) It’s a wonderful feature: a format that lets you store blobs in the lingua franca of modern web services, without requiring re-parsing whenever you want to access a field, and in a way that. Search_field text COLLATE pg_catalog."default", PostgreSQL introduced the JSONB type in 9.4 with considerable celebration. Updated_at timestamp without time zone DEFAULT now(),Ĭategories text COLLATE pg_catalog."default", Gender text COLLATE pg_catalog."default",Ĭreated_at timestamp without time zone DEFAULT now(), Id integer NOT NULL DEFAULT nextval('influencers_id_seq'::regclass), This is our database and indexes created: CREATE TABLE public.influencers FebruToday, we're going to talk about JSONB and how JSONB performance can behave badly when the JSONB value exceeds the two kilobyte limit where Postgres starts storing data in TOAST. Which indicates (as I see it) that no index is being used. This is the query I'm testing: SELECT "public"."influencers".*Īnd after running EXPLAIN: -> Seq Scan on influencers (cost=0.14 rows=216 width=1110) That is why it got a lot faster than in the first versions. Furthermore, Postgres got BSON support and other additions for handling JSON, that did improve performance considerably. For some reason it seems as Postgres is not using the index we created. A key reason is: Postgres has good indexes, while indexes in MongoDB are not worth it.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |