Jira (PDB-5253) Investigate if we can we add indexes on nested facts

Title: Message Title

Used this query with explain analyze for the nested fact os.lsb.distid:

EXPLAIN analyse select (stable||volatile->'os'->'lsb'->>'distid') from factsets where ((stable||volatile)->'os'->'lsb'->>'distid') ~ '.*MND';

The result shows it performed a sequencial scan:

Seq Scan on factsets  (cost=0.00..50.22 rows=72 width=32) (actual time=1.850..34.532 rows=1 loops=1)

  Filter: (((((stable || volatile) -> 'os'::text) -> 'lsb'::text) ->> 'distid'::text) ~ '.*MND'::text)

  Rows Removed by Filter: 999

Planning time: 3.502 ms

Execution time: 34.757 ms

After that I created an index for that fact with:

CREATE INDEX dist_id_values_idx ON factsets USING GIN (((stable||volatile)->'os'->'lsb'->>'distid') gin_trgm_ops);

Running the same explain analyse query resulted:

Bitmap Heap Scan on factsets  (cost=8.56..37.90 rows=72 width=32) (actual time=0.474..0.476 rows=1 loops=1)

  Recheck Cond: (((((stable || volatile) -> 'os'::text) -> 'lsb'::text) ->> 'distid'::text) ~ '.*MND'::text)

  Heap Blocks: exact=1

  ->  Bitmap Index Scan on dist_id_values_idx  (cost=0.00..8.54 rows=72 width=0) (actual time=0.355..0.355 rows=1 loops=1)

        Index Cond: (((((stable || volatile) -> 'os'::text) -> 'lsb'::text) ->> 'distid'::text) ~ '.*MND'::text)

Planning time: 27.009 ms

Execution time: 0.764 ms

It shows that the index is used.

This message was sent by Atlassian Jira

--
You received this message because you are subscribed to the Google Groups "Puppet Bugs" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [email protected].
To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.413374.1629878715000.117177.1629974220105%40Atlassian.JIRA.

Reply via email to