Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[YSQL][Query Diagnostics] Collect Explain plans #22150

Closed
1 task done
Tracked by #22155
IshanChhangani opened this issue Apr 25, 2024 · 0 comments
Closed
1 task done
Tracked by #22155

[YSQL][Query Diagnostics] Collect Explain plans #22150

IshanChhangani opened this issue Apr 25, 2024 · 0 comments
Assignees
@IshanChhangani
Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue

Comments

@IshanChhangani
Copy link
Contributor

IshanChhangani commented Apr 25, 2024 •
edited
Loading

Jira Link: DB-11078

Description

This issue focuses on adding functionality to extract and integrate data from EXPLAIN into the query diagnostics framework. This enhancement will support the parameters related to the Explain plan within the yb_query_diagnostics().

Issue Type

kind/enhancement

Warning: Please confirm that this issue does not contain any sensitive information

  • I confirm this issue does not contain any sensitive information.
@IshanChhangani IshanChhangani added area/ysql Yugabyte SQL (YSQL) status/awaiting-triage Issue awaiting triage labels Apr 25, 2024
@yugabyte-ci yugabyte-ci added kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue labels Apr 25, 2024
@IshanChhangani IshanChhangani mentioned this issue Apr 25, 2024
Open
11 tasks
@hbhanawat hbhanawat removed the status/awaiting-triage Issue awaiting triage label Apr 25, 2024
IshanChhangani added a commit that referenced this issue Sep 5, 2024
@IshanChhangani
[#22150] YSQL, QueryDiagnostics: EXPLAIN (ANALYZE, DIST) support for …
40689bc 
…queryDiagnostics

Summary:
- This diff adds functionality for `explain_analyze` and `explain_dist` params.
- Note: This diff does not include code for supporting EXPLAIN(ANALYZE, DIST, DEBUG)
- Explain.txt looks like:-

```
duration: 6.407 ms
plan:
Sort  (cost=550.02..550.85 rows=333 width=116) (actual time=6.390..6.390 rows=0 loops=1)
  Sort Key: (sum(o.total_amount)) DESC, (count(DISTINCT o.order_id)) DESC
  Sort Method: quicksort  Memory: 25kB
  ->  GroupAggregate  (cost=496.90..536.07 rows=333 width=116) (actual time=6.377..6.377 rows=0 loops=1)
        Group Key: c.customer_id, ((((c.first_name)::text || ' '::text) || (c.last_name)::text))
        Filter: (count(DISTINCT o.order_id) > 0)
        ->  Sort  (cost=496.90..499.40 rows=1000 width=178) (actual time=6.374..6.374 rows=0 loops=1)
              Sort Key: c.customer_id, ((((c.first_name)::text || ' '::text) || (c.last_name)::text))
              Sort Method: quicksort  Memory: 25kB
              ->  YB Batched Nested Loop Left Join  (cost=117.50..447.07 rows=1000 width=178) (actual time=6.362..6.362 rows=0 loops=1)
                    Join Filter: (oi.product_id = p.product_id)
                    ->  YB Batched Nested Loop Join  (cost=117.50..328.19 rows=1000 width=268) (actual time=6.359..6.359 rows=0 loops=1)
                          Join Filter: (o.customer_id = c.customer_id)
                          ->  Hash Right Join  (cost=117.50..225.69 rows=1000 width=32) (actual time=6.354..6.355 rows=0 loops=1)
                                Hash Cond: (oi.order_id = o.order_id)
                                ->  Seq Scan on order_items oi  (cost=0.00..100.00 rows=1000 width=12) (never executed)
                                ->  Hash  (cost=105.00..105.00 rows=1000 width=24) (actual time=6.348..6.348 rows=0 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                      ->  Seq Scan on orders o  (cost=0.00..105.00 rows=1000 width=24) (actual time=6.345..6.345 rows=0 loops=1)
                                            Storage Filter: ((order_date >= '2023-04-01'::date) AND (order_date <= '2023-05-31'::date))
                                            Storage Table Read Requests: 1
                                            Storage Table Read Execution Time: 0.000 ms
                          ->  Index Scan using customers_pkey on customers c  (cost=0.00..0.12 rows=1 width=240) (never executed)
                                Index Cond: (customer_id = ANY (ARRAY[o.customer_id, $1, $2, ..., $1023]))
                                Storage Filter: (registration_date >= '2023-01-01'::date)
                    ->  Index Scan using products_pkey on products p  (cost=0.00..0.11 rows=1 width=122) (never executed)
                          Index Cond: (product_id = ANY (ARRAY[oi.product_id, $1025, $1026, ..., $2047]))

duration: 6.559 ms
plan:
Sort  (cost=550.02..550.85 rows=333 width=116) (actual time=6.537..6.537 rows=0 loops=1)
  Sort Key: (sum(o.total_amount)) DESC, (count(DISTINCT o.order_id)) DESC
  Sort Method: quicksort  Memory: 25kB
  ->  GroupAggregate  (cost=496.90..536.07 rows=333 width=116) (actual time=6.526..6.526 rows=0 loops=1)
        Group Key: c.customer_id, ((((c.first_name)::text || ' '::text) || (c.last_name)::text))
        Filter: (count(DISTINCT o.order_id) > 0)
        ->  Sort  (cost=496.90..499.40 rows=1000 width=178) (actual time=6.523..6.523 rows=0 loops=1)
              Sort Key: c.customer_id, ((((c.first_name)::text || ' '::text) || (c.last_name)::text))
              Sort Method: quicksort  Memory: 25kB
              ->  YB Batched Nested Loop Left Join  (cost=117.50..447.07 rows=1000 width=178) (actual time=6.515..6.515 rows=0 loops=1)
                    Join Filter: (oi.product_id = p.product_id)
                    ->  YB Batched Nested Loop Join  (cost=117.50..328.19 rows=1000 width=268) (actual time=6.511..6.511 rows=0 loops=1)
                          Join Filter: (o.customer_id = c.customer_id)
                          ->  Hash Right Join  (cost=117.50..225.69 rows=1000 width=32) (actual time=6.506..6.506 rows=0 loops=1)
                                Hash Cond: (oi.order_id = o.order_id)
                                ->  Seq Scan on order_items oi  (cost=0.00..100.00 rows=1000 width=12) (never executed)
                                ->  Hash  (cost=105.00..105.00 rows=1000 width=24) (actual time=6.497..6.497 rows=0 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                      ->  Seq Scan on orders o  (cost=0.00..105.00 rows=1000 width=24) (actual time=6.494..6.494 rows=0 loops=1)
                                            Storage Filter: ((order_date >= '2023-04-01'::date) AND (order_date <= '2023-05-31'::date))
                                            Storage Table Read Requests: 1
                                            Storage Table Read Execution Time: 0.000 ms
                          ->  Index Scan using customers_pkey on customers c  (cost=0.00..0.12 rows=1 width=240) (never executed)
                                Index Cond: (customer_id = ANY (ARRAY[o.customer_id, $1, $2, ..., $1023]))
                                Storage Filter: (registration_date >= '2023-01-01'::date)
                    ->  Index Scan using products_pkey on products p  (cost=0.00..0.11 rows=1 width=122) (never executed)
                          Index Cond: (product_id = ANY (ARRAY[oi.product_id, $1025, $1026, ..., $2047]))

```
Jira: DB-11078

Test Plan:
./yb_build.sh --java-test TestYbQueryDiagnostics#checkExplainData

Testing with a large query that generates an EXPLAIN plan exceeding 16KB, causes output termination and loss of remaining data. This is expected and will be handled later  [[ #23720 | #23720 ]]

Reviewers: asaha, hbhanawat, telgersma

Reviewed By: telgersma

Subscribers: yql, ybase

Differential Revision: https://phorge.dev.yugabyte.com/D35566
jasonyb pushed a commit that referenced this issue Sep 6, 2024
@yugabyte-ci @jaki
[BACKPORT pg15-cherrypicks] all: Bulk port from master - 94
1463ddd 
Summary:
 c587efd [docs] minor edit (#23796)
 31e09f3 [PLAT-15029] yba installer split data and software directory setup
 f5ba17d [PLAT-15039]: Fix bootstrap on bi-directional xCluster config creation
 578248a [#23770] YSQL: Deterministically populate catalog cache in tests with Connection Manager enabled
 7b1f22a [#23799] test: Fixed PgTableSizeTest.SharedTableSize test for pg15
 788434a [#18771, #21352] docdb: Fix LightweightMessage max size when parsing
 02ced43 [#22821] YSQL: Preserve local limit in a multi-page read
 50ff737 [#23741] docdb: Fix cloning of colocated databases with only parent table
 Excluded: 9889df7 [#23706] YSQL: Add table-level catcache Prometheus metrics
 c770d79 [#23747] MetaCache: Callback should not be called while holding the lock
 Excluded: 40689bc [#22150] YSQL, QueryDiagnostics:  EXPLAIN (ANALYZE, DIST) support for queryDiagnostics
 1655e69 [PLAT-15148]: Set XCluster Table Status to DroppedFromTarget if table in replication is dropped from target only
 16262f7 [#22519] YSQL: Simplify API of the ExplicitRowLockBuffer class
 6614afb [PLAT-14958][PLAT-14959] Make ssh fields optional if skipProvisioning is true
 1153b56 [PLAT-14867] Make sure restart alerts don't trigger for small time updates during NTP sync
 bf1c7bc [PLAT-12226] Add connection pooling status to universe health check
 38d8ae8 [PLAT-14805]Support adding EAR configs
 a180bef [#19134] YSQL, ASH: Setting ASH circular buffer size based on the number of cores
 7d8fc76 Adjust heading link (#23807)
 4c6cf5a [PLAT-4899]Basic validation of certificates
 f24eb10 [#23787] YSQL: Avoid executing conn mgr guc variables hooks for parallel workers
 ee18df8 [PLAT-13921] [K8] [UI] Universe action tasks are disabled after a failed shrink rr node task
 Excluded: dcf1821 [#23797] YSQL: Modify some tests to run in single connection mode with Connection Manager
 0ac22cd [Docs] Remove Drift chat bot (#23802)
 0e91003 [#22825] DocDB: Vector Index General Read Path with DummyANN
 e8f09b5 [PLAT-15175] Make runtime conf for skipping cluster consistency check public
 ee479ee Versionwarning (#23781)
 a05c6a3 [DB-12681] yugabyted-ui: Add Voyager commands to different Voyager phases in the UI.
 cc80d59 [#23777] yugabyted: updating the pg parity testcase to reflect the new gflags enabled for the pg parity feature.

Test Plan: Jenkins: rebase: pg15-cherrypicks

Reviewers: jason, tfoucher

Subscribers: yql

Differential Revision: https://phorge.dev.yugabyte.com/D37822
IshanChhangani added a commit that referenced this issue Sep 12, 2024
@IshanChhangani
[BACKPORT pg15-cherrypicks][#22150] YSQL, QueryDiagnostics: EXPLAIN (…
ec1c068 
…ANALYZE, DIST) support for queryDiagnostics

Summary:
No merge conflict
- yb_query_diagnostics.c
  - YbQueryDiagnostics_ExecutorStart
    - Resolved compile errors by replacing `MAX_REMOVE_VALUE` with pg15 counterpart function `pg_prng_double(&pg_global_prng_state)`. Upstream PG 3804539e48e794781c6145c7f988f5d507418fa8 replaced random function with pg_prng_double.  YB master 40689bc
  - Imported this function `include "common/pg_prng.h"`
    - same

original summary
- This diff adds functionality for `explain_analyze` and `explain_dist` params.
- Note: This diff does not include code for supporting EXPLAIN(ANALYZE, DIST, DEBUG)
- Explain.txt looks like:-

```
duration: 6.407 ms
plan:
Sort  (cost=550.02..550.85 rows=333 width=116) (actual time=6.390..6.390 rows=0 loops=1)
  Sort Key: (sum(o.total_amount)) DESC, (count(DISTINCT o.order_id)) DESC
  Sort Method: quicksort  Memory: 25kB
  ->  GroupAggregate  (cost=496.90..536.07 rows=333 width=116) (actual time=6.377..6.377 rows=0 loops=1)
        Group Key: c.customer_id, ((((c.first_name)::text || ' '::text) || (c.last_name)::text))
        Filter: (count(DISTINCT o.order_id) > 0)
        ->  Sort  (cost=496.90..499.40 rows=1000 width=178) (actual time=6.374..6.374 rows=0 loops=1)
              Sort Key: c.customer_id, ((((c.first_name)::text || ' '::text) || (c.last_name)::text))
              Sort Method: quicksort  Memory: 25kB
              ->  YB Batched Nested Loop Left Join  (cost=117.50..447.07 rows=1000 width=178) (actual time=6.362..6.362 rows=0 loops=1)
                    Join Filter: (oi.product_id = p.product_id)
                    ->  YB Batched Nested Loop Join  (cost=117.50..328.19 rows=1000 width=268) (actual time=6.359..6.359 rows=0 loops=1)
                          Join Filter: (o.customer_id = c.customer_id)
                          ->  Hash Right Join  (cost=117.50..225.69 rows=1000 width=32) (actual time=6.354..6.355 rows=0 loops=1)
                                Hash Cond: (oi.order_id = o.order_id)
                                ->  Seq Scan on order_items oi  (cost=0.00..100.00 rows=1000 width=12) (never executed)
                                ->  Hash  (cost=105.00..105.00 rows=1000 width=24) (actual time=6.348..6.348 rows=0 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                      ->  Seq Scan on orders o  (cost=0.00..105.00 rows=1000 width=24) (actual time=6.345..6.345 rows=0 loops=1)
                                            Storage Filter: ((order_date >= '2023-04-01'::date) AND (order_date <= '2023-05-31'::date))
                                            Storage Table Read Requests: 1
                                            Storage Table Read Execution Time: 0.000 ms
                          ->  Index Scan using customers_pkey on customers c  (cost=0.00..0.12 rows=1 width=240) (never executed)
                                Index Cond: (customer_id = ANY (ARRAY[o.customer_id, $1, $2, ..., $1023]))
                                Storage Filter: (registration_date >= '2023-01-01'::date)
                    ->  Index Scan using products_pkey on products p  (cost=0.00..0.11 rows=1 width=122) (never executed)
                          Index Cond: (product_id = ANY (ARRAY[oi.product_id, $1025, $1026, ..., $2047]))

duration: 6.559 ms
plan:
Sort  (cost=550.02..550.85 rows=333 width=116) (actual time=6.537..6.537 rows=0 loops=1)
  Sort Key: (sum(o.total_amount)) DESC, (count(DISTINCT o.order_id)) DESC
  Sort Method: quicksort  Memory: 25kB
  ->  GroupAggregate  (cost=496.90..536.07 rows=333 width=116) (actual time=6.526..6.526 rows=0 loops=1)
        Group Key: c.customer_id, ((((c.first_name)::text || ' '::text) || (c.last_name)::text))
        Filter: (count(DISTINCT o.order_id) > 0)
        ->  Sort  (cost=496.90..499.40 rows=1000 width=178) (actual time=6.523..6.523 rows=0 loops=1)
              Sort Key: c.customer_id, ((((c.first_name)::text || ' '::text) || (c.last_name)::text))
              Sort Method: quicksort  Memory: 25kB
              ->  YB Batched Nested Loop Left Join  (cost=117.50..447.07 rows=1000 width=178) (actual time=6.515..6.515 rows=0 loops=1)
                    Join Filter: (oi.product_id = p.product_id)
                    ->  YB Batched Nested Loop Join  (cost=117.50..328.19 rows=1000 width=268) (actual time=6.511..6.511 rows=0 loops=1)
                          Join Filter: (o.customer_id = c.customer_id)
                          ->  Hash Right Join  (cost=117.50..225.69 rows=1000 width=32) (actual time=6.506..6.506 rows=0 loops=1)
                                Hash Cond: (oi.order_id = o.order_id)
                                ->  Seq Scan on order_items oi  (cost=0.00..100.00 rows=1000 width=12) (never executed)
                                ->  Hash  (cost=105.00..105.00 rows=1000 width=24) (actual time=6.497..6.497 rows=0 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                      ->  Seq Scan on orders o  (cost=0.00..105.00 rows=1000 width=24) (actual time=6.494..6.494 rows=0 loops=1)
                                            Storage Filter: ((order_date >= '2023-04-01'::date) AND (order_date <= '2023-05-31'::date))
                                            Storage Table Read Requests: 1
                                            Storage Table Read Execution Time: 0.000 ms
                          ->  Index Scan using customers_pkey on customers c  (cost=0.00..0.12 rows=1 width=240) (never executed)
                                Index Cond: (customer_id = ANY (ARRAY[o.customer_id, $1, $2, ..., $1023]))
                                Storage Filter: (registration_date >= '2023-01-01'::date)
                    ->  Index Scan using products_pkey on products p  (cost=0.00..0.11 rows=1 width=122) (never executed)
                          Index Cond: (product_id = ANY (ARRAY[oi.product_id, $1025, $1026, ..., $2047]))

```
Jira: DB-11078

Original commit: 40689bc / D35566

Test Plan:
./yb_build.sh --java-test TestYbQueryDiagnostics#checkExplainData

Testing with a large query that generates an EXPLAIN plan exceeding 16KB, causes output termination and loss of remaining data. This is expected and will be handled later  [[ #23720 | #23720 ]]

Reviewers: jason, tfoucher

Reviewed By: jason

Subscribers: ybase, yql

Differential Revision: https://phorge.dev.yugabyte.com/D37980
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@IshanChhangani IshanChhangani

Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@hbhanawat @yugabyte-ci @IshanChhangani