create table testp(id SERIAL, field JSONB);
create index price on testp ((field->>'price'));
insert into testp VALUES(DEFAULT, '{"price": 50, "name": "Smth"}');
explain analyze select field->>'price' from testp;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on testp (cost=0.00..25.38 rows=1230 width=32) (actual time=0.011..0.012 rows=1 loops=1)
Planning time: 0.040 ms
Execution time: 0.023 ms
(3 rows)
-
For just a single row there is absolutely no benefit whatsoever to use the index - this is true for any index not just indexed JSON documentsuser330315– user3303152015-07-31 10:12:54 +00:00Commented Jul 31, 2015 at 10:12
Add a comment
|
1 Answer
Just because the index exists does not mean that it will get used. The query planner tries to minimize the cost of query execution (which is listed in the EXPLAIN output). The cost is (much simplified) IO and CPU, with IO typically being 100 times more expensive than CPU. So the query planner favours a sequential scan over a few pages of rows over getting the additional pages that hold the index. Only when your table becomes very large will the query planner use any appropriate indexes.
In case of your query, the index would never be used, no matter the size of the table, because there is no filtering or joining on: you are simply asking for all rows to be returned.
Load your data with lots of random data and then try again will some filtering:
INSERT INTO testp(field)
SELECT json_build_object('price', (random() * 1000)::int, 'name', md5(random()::text))::jsonb
FROM generate_series(1, 1000000);
But even with 1,000,000 rows an index is really only used in more complex queries.
A more troublesome issue is that your index is on a jsonb value. The index will only get used when you compare it to another jsonb value. If your 'price' field contains an integer you would need an index like:
CREATE INDEX price_int ON testp (CAST(CAST(field->>'price' AS text) AS int));
3 Comments
florinp
Thanks Patrick. I populated with 4 million rows of entries and used the first version of CREATE INDEX without the CAST, but it was still doing seq scan.
florinp
After I put the CAST, it was using the index. Indeed, it shouldn't use the index when the cost isn't worth it.
florinp
Still, for the GIN index, it uses it even if you have a few entries, with expression like : SELECT * FROM field @> '{price: 20}'; but I think that's the purpose with GIN in the first place.