Jira (PDB-5253) Investigate if we can we add indexes on nested facts
Title: Message Title Andrei Filipovici updated an issue PuppetDB / PDB-5253 Investigate if we can we add indexes on nested facts Change By: Andrei Filipovici Attachment: query_plan_local.json Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- 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 puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.413374.1629878715000.117193.1629976140031%40Atlassian.JIRA.
Jira (PDB-5253) Investigate if we can we add indexes on nested facts
Title: Message Title Andrei Filipovici commented on PDB-5253 Re: Investigate if we can we add indexes on nested facts We can use the nested index from PDB if we use the dotted projections on the inventory endpoint. An example would be: ["~", "facts.os.lsb.distid", "MND.*"] The query plan for the above query is in the attachement. If we use the fact-contents endpoint with a query like this: ["and", ["=", "path", ["os", "lsb", "distid"]], ["~", "value", "MND.*"]] the index will not be used. Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97)
Jira (PDB-5253) Investigate if we can we add indexes on nested facts
Title: Message Title Andrei Filipovici commented on PDB-5253 Re: Investigate if we can we add indexes on nested facts 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:
Jira (PDB-5253) Investigate if we can we add indexes on nested facts
Title: Message Title Bogdan Irimie assigned an issue to Andrei Filipovici PuppetDB / PDB-5253 Investigate if we can we add indexes on nested facts Change By: Bogdan Irimie Assignee: Andrei Filipovici Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- 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 puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.413374.1629878715000.117123.1629962160044%40Atlassian.JIRA.
Jira (PDB-5253) Investigate if we can we add indexes on nested facts
Title: Message Title Bogdan Irimie updated an issue PuppetDB / PDB-5253 Investigate if we can we add indexes on nested facts Change By: Bogdan Irimie Sprint: ghost-08.09.2021 Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- 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 puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.413374.1629878715000.116168.1629878820173%40Atlassian.JIRA.
Jira (PDB-5253) Investigate if we can we add indexes on nested facts
Title: Message Title Bogdan Irimie updated an issue PuppetDB / PDB-5253 Investigate if we can we add indexes on nested facts Change By: Bogdan Irimie Story Points: 2 Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- 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 puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.413374.1629878715000.116167.1629878820118%40Atlassian.JIRA.
Jira (PDB-5253) Investigate if we can we add indexes on nested facts
Title: Message Title Bogdan Irimie created an issue PuppetDB / PDB-5253 Investigate if we can we add indexes on nested facts Issue Type: Task Assignee: Unassigned Created: 2021/08/25 1:05 AM Priority: Normal Reporter: Bogdan Irimie Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- 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 puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit
