Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iid8J-0002ol-3y for pgsql-bugs@arkaria.postgresql.org; Sat, 21 Dec 2019 11:41:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iid7H-0001g3-CR for pgsql-bugs@arkaria.postgresql.org; Sat, 21 Dec 2019 11:40:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iid7G-0001fw-LS for pgsql-bugs@lists.postgresql.org; Sat, 21 Dec 2019 11:40:14 +0000 Received: from mail-wm1-x342.google.com ([2a00:1450:4864:20::342]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iid7C-00007O-28 for pgsql-bugs@lists.postgresql.org; Sat, 21 Dec 2019 11:40:13 +0000 Received: by mail-wm1-x342.google.com with SMTP id p9so11435623wmc.2 for ; Sat, 21 Dec 2019 03:40:09 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=KJ0fZsYzaUjZVprDR15uVprMeZB3RX8oCoOSyj3mDB4=; b=tPXYV48xddY9nMR+6bPHEzHiPwKrtt5nImi3fXiVi7TQmWQqtclT5gHdp4AVcbvKID /zq174u7RKQv81I7JrvSZbd69rHF6BDek6Esk81mODYPIMobVokDYM09f9tS8/4nFtRt 8ZxxUALGk2aFqb2jJ3w+4kPY1w0Wo/iWsx86NMjIOpmTcT0WhY6puIoefJjD0j0ApioW KydG4ffmtgCeDyM2asxPXMBigG9xYF35ARsgK+nxLHMvdf3PeF36JkkypbL8AZWsd2uy J0Qwf75Xo1o6LjlG0kcQsPDDtePTgZW9BInhXuvDEn5xwpj0xf37rhe6ug3PMRVdnE8p U8XQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=KJ0fZsYzaUjZVprDR15uVprMeZB3RX8oCoOSyj3mDB4=; b=Vv72iCXUmECj5CqgE+BINvz604jjYwHf7U0LMiu7d+EvY+NeKD1P4h15Ie1hCf/mLj JosB39SNwYVeEUs+yZG6OiMYDggySvtF3cckQbzfNDk/dhtEXro7VaVRpnzNS65wQ5uy vBT8QrPYSFKuK/p5J8yxAalV1XSeKHR2CLLDTZnyhQLFMA+Uu3qMnN1HtlK4/G2GqMvy rofU6FLzwJiz7vVsbZei5JdgI922cdlQcu8zWNruRSEGydWrSt6IbGRbXz8Xlfu0JKFW 4Xs25UY9Olowl5vy/WdSTqulSeGEUS6m/1s97eXOV6ePywHii1f/XPW2jVM+zaBVkeW9 eEhQ== X-Gm-Message-State: APjAAAU73VfwRSNQzYs38kvlfXBLvIwZ/VWgOravXJRpZ0rErIh+79Lc /MKmOCp1xIGhLNHBw2+ozsjM/g== X-Google-Smtp-Source: APXvYqwhUb8c4KmHkyzOUy1pGBVyaCx+jJHNhrTNsD/hY8STh5oQHitm+e44/0dE5t1HpGRpL7JLIQ== X-Received: by 2002:a1c:f407:: with SMTP id z7mr2472066wma.72.1576928405955; Sat, 21 Dec 2019 03:40:05 -0800 (PST) Received: from localhost (ip-86-49-253-92.net.upcbroadband.cz. [86.49.253.92]) by smtp.gmail.com with ESMTPSA id r5sm12547196wrt.43.2019.12.21.03.40.04 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Sat, 21 Dec 2019 03:40:05 -0800 (PST) Date: Sat, 21 Dec 2019 12:40:03 +0100 From: Tomas Vondra To: Zhihong Zhang Cc: pgsql-bugs@lists.postgresql.org Subject: Re: Indexing on JSONB field not working Message-ID: <20191221114003.yo56lv3o6thdvxtn@development> References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Fri, Dec 20, 2019 at 08:15:39PM -0500, Zhihong Zhang wrote: >Run those 2 EXPLAINs with seqscan off and on. See what difference it makes! > > >SET enable_seqscan = off; >explain analyze select id, _doc->>'floatValue' from assets where (_doc #> '{floatValue}'::text[])::double precision < 3.0 limit 100; > >"Limit (cost=0.43..313.25 rows=100 width=53) (actual time=0.092..0.236 rows=7 loops=1)" >" -> Index Scan using assets_floatvalue_idx on assets (cost=0.43..2714072.57 rows=867607 width=53) (actual time=0.089..0.230 rows=7 loops=1)" >" Index Cond: (((_doc #> '{floatValue}'::text[]))::double precision < '3'::double precision)" >"Planning Time: 1.617 ms" >"Execution Time: 0.276 ms" > >SET enable_seqscan = on; >explain analyze select id, _doc->>'floatValue' from assets where (_doc #> '{floatValue}'::text[])::double precision < 3.0 limit 100; > >"Limit (cost=0.00..107.95 rows=100 width=53) (actual time=41021.311..313501.746 rows=7 loops=1)" >" -> Seq Scan on assets (cost=0.00..936605.40 rows=867607 width=53) (actual time=41021.309..313501.732 rows=7 loops=1)" >" Filter: (((_doc #> '{floatValue}'::text[]))::double precision < '3'::double precision)" >" Rows Removed by Filter: 2602824" >"Planning Time: 0.283 ms" >"Execution Time: 313501.777 ms" > Well, this confirms what I suspected before - the optimizer believes the seqscan plan is a bit cheaper compared to index scan (107 vs. 313) but that mismatches the actual performance. The question is why ... For LIMIT queries, I can think of two common issues - the cost estimate is computed as a simple linear approximation in the input relation. For example, we know the seqscan is expected to produce 867607 rows with a total cost of 936605, so the cost of producing just 100 rows is 100 * 936605 / 867607 = 107.95 But that assumes a number of things: (a) that the seqscan row estimate is correct, and that (b) the matching rows are uniformly distributed in the table. If it's misestimated, or if the rows are towards the end of the relation (i.e. after doing a most of the costed work) this estimate may be quite off. Can you do explain analyze of the query without the LIMIT? BTW a LIMIT without an ORDER BY is a bit strange. Also, maybe you could do try using a partial index (if the where condition does not change). regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services