Jira (PDB-5647) Slow fact queries after PuppetDB 7 upgrade (comparing with PuppetDB 5) with GCP CloudSQL PostgreSQL

['Krzysztof Krzyżaniak' via Puppet Bugs]

Title: Message Title

Krzysztof Krzyżaniak commented on PDB-5647   Re: Slow fact queries after PuppetDB 7 upgrade (comparing with PuppetDB 5) with GCP CloudSQL PostgreSQL We can confirm that moving from CloudSQL to PostgreSQL on VM improves query time but still not good enough for our needs.                                                                            QUERY PLAN                                                                             -----------------------------------------------------------------------------------------------------------------------------------------------------------------  Nested Loop Semi Join  (cost=12495.76..37341.86 rows=1 width=32) (actual time=10829.491..25654.661 rows=3 loops=1)    Join Filter: (factsets.certname = fs_2.certname)    Rows Removed by Join Filter: 1307    Buffers: shared hit=620993 read=24568 dirtied=27 written=2007    ->  Nested Loop Semi Join  (cost=12495.76..24923.36 rows=1 width=258) (actual time=10783.305..25178.060 rows=3 loops=1)          Join Filter: (factsets.certname = fs_1.certname)          Rows Removed by Join Filter: 4326          Buffers: shared hit=608386 read=24568 dirtied=27 written=2007          ->  Nested Loop  (cost=12495.76..12504.87 rows=1 width=203) (actual time=986.376..993.239 rows=27 loops=1)                Buffers: shared hit=14055 read=11105 dirtied=27 written=1564                ->  HashAggregate  (cost=12418.48..12418.49 rows=1 width=63) (actual time=985.937..985.982 rows=27 loops=1)                      Group Key: fs.certname                      Batches: 1  Memory Usage: 24kB                      Buffers: shared hit=13708 read=11092 dirtied=27 written=1564                      ->  Subquery Scan on fs  (cost=0.00..12418.48 rows=1 width=63) (actual time=46.545..985.901 rows=27 loops=1)                            Filter: ((fs.key = 'egnyte_role'::text) AND (fs.value = '"roles-consul"'::jsonb) AND (jsonb_typeof(fs.value) = 'string'::text))                            Rows Removed by Filter: 639154                            Buffers: shared hit=13708 read=11092 dirtied=27 written=1564                            ->  Result  (cost=0.00..6970.48 rows=272400 width=127) (actual time=0.394..951.928 rows=639181 loops=1)                                  Buffers: shared hit=13708 read=11092 dirtied=27 written=1564                                  ->  ProjectSet  (cost=0.00..1522.48 rows=272400 width=95) (actual time=0.392..884.611 rows=639181 loops=1)                                        Buffers: shared hit=13708 read=11092 dirtied=27 written=1564                                        ->  Seq Scan on factsets factsets_1  (cost=0.00..133.24 rows=2724 width=156) (actual time=0.011..1.002 rows=2699 loops=1)                                              Buffers: shared hit=39 read=67 dirtied=6 written=10                ->  Index Scan using factsets_certname_idx on factsets  (cost=77.28..85.31 rows=1 width=156) (actual time=0.265..0.265 rows=1 loops=27)                      Index Cond: (certname = fs.certname)                      Filter: ((NOT (hashed SubPlan 1)) AND ((stable || volatile) ? 'consul_services'::text))                      Buffers: shared hit=345 read=13                      SubPlan 1                        ->  Seq Scan on certnames  (cost=0.00..77.00 rows=1 width=55) (actual time=0.190..0.191 rows=2 loops=1)                              Filter: ((deactivated IS NOT NULL) OR (expired IS NOT NULL))                              Rows Removed by Filter: 2699                              Buffers: shared hit=50          ->  Subquery Scan on fs_1  (cost=0.00..12418.48 rows=1 width=63) (actual time=1.066..895.696 rows=160 loops=27)                Filter: ((fs_1.key = 'virtual_dc'::text) AND (fs_1.value = '"egn"'::jsonb) AND (jsonb_typeof(fs_1.value) = 'string'::text))                Rows Removed by Filter: 579366                Buffers: shared hit=594331 read=13463 written=443                ->  Result  (cost=0.00..6970.48 rows=272400 width=127) (actual time=0.305..865.161 rows=579526 loops=27)                      Buffers: shared hit=594331 read=13463 written=443                      ->  ProjectSet  (cost=0.00..1522.48 rows=272400 width=95) (actual time=0.304..803.625 rows=579526 loops=27)                            Buffers: shared hit=594331 read=13463 written=443                            ->  Seq Scan on factsets factsets_2  (cost=0.00..133.24 rows=2724 width=156) (actual time=0.003..0.483 rows=2448 loops=27)                                  Buffers: shared hit=2468 read=129 written=2    ->  Subquery Scan on fs_2  (cost=0.00..12418.48 rows=1 width=63) (actual time=0.350..158.505 rows=437 loops=3)          Filter: ((fs_2.key = 'side'::text) AND (fs_2.value = '"gcp"'::jsonb) AND (jsonb_typeof(fs_2.value) = 'string'::text))          Rows Removed by Filter: 101793          Buffers: shared hit=12580          ->  Result  (cost=0.00..6970.48 rows=272400 width=127) (actual time=0.344..153.210 rows=102230 loops=3)                Buffers: shared hit=12580                ->  ProjectSet  (cost=0.00..1522.48 rows=272400 width=95) (actual time=0.343..142.446 rows=102230 loops=3)                      Buffers: shared hit=12580                      ->  Seq Scan on factsets factsets_3  (cost=0.00..133.24 rows=2724 width=156) (actual time=0.003..0.077 rows=437 loops=3)                            Buffers: shared hit=53  Planning:    Buffers: shared hit=21 read=3  Planning Time: 0.653 ms  Execution Time: 25654.807 ms (57 rows) Add Comment   This message was sent by Atlassian Jira (v8.20.21#820021-sha1:38274c8) -- 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.486416.1685707350000.4561.1685952540030%40Atlassian.JIRA.