dbsenpai — query analysis report for shop

# 8813421760540345742

🔍 Visual Plan

Count

48210

Avg ms

412.18

Total

19871197.80

Updated

All Columns

count

48210

average

412.18

total

19871197.80

rows_returned

4200

min_ms

123.65

max_ms

2514.30

stddev_ms

329.74

cache_hit_pct

61.20

blocks_read

1900000

temp_blocks_written

0

role_name

app_rw

Query with Placeholders

SELECT * FROM orders WHERE customer_id = $1 AND status = $2 ORDER BY created_at DESC

Placeholder Values (0)

#	Value
No placeholders

Full Resolved Query✎ editable

Query Execution Result(EXPLAIN ANALYZE JSON — from --run_queries)

[
  {
    "Plan": {
      "Node Type": "Sort",
      "Startup Cost": 98211.4,
      "Total Cost": 98212.1,
      "Plan Rows": 260,
      "Plan Width": 188,
      "Actual Startup Time": 409.2,
      "Actual Total Time": 409.4,
      "Actual Rows": 4200,
      "Actual Loops": 1,
      "Sort Key": [
        "created_at DESC"
      ],
      "Sort Method": "quicksort",
      "Sort Space Used": 1180,
      "Sort Space Type": "Memory",
      "Plans": [
        {
          "Node Type": "Seq Scan",
          "Parallel Aware": false,
          "Relation Name": "orders",
          "Schema": "public",
          "Alias": "orders",
          "Startup Cost": 0.0,
          "Total Cost": 98201.0,
          "Plan Rows": 260,
          "Plan Width": 188,
          "Actual Startup Time": 0.9,
          "Actual Total Time": 401.7,
          "Actual Rows": 4200,
          "Actual Loops": 1,
          "Filter": "((customer_id = $1) AND (status = $2))",
          "Rows Removed by Filter": 4195800,
          "Shared Hit Blocks": 41200,
          "Shared Read Blocks": 39100
        }
      ]
    },
    "Planning Time": 0.31,
    "Triggers": [],
    "Execution Time": 411.9
  }
]

📊 Table Intelligence(1 table(s) — stats, indexes, dead tuples, vacuum/analyze history)

📌 orders2 index(es)

Dead Tuples %

11.8

Dead Tuple Size

?

Live Tuples

4200000

Dead Tuples

560000

Total Size

3.1 GB

Table Size

?

Index Size

?

Mods Since Analyze

2100000

Seq Scans

58010

Last Analyze

?

Last Autoanalyze

9 days ago

Last Vacuum

?

Last Autovacuum

2 days ago

Indexes

Index Name	Size	Scans	Tuples Read	Definition
PK orders_pkey	120 MB	910000		CREATE INDEX orders_pkey ON public.orders USING btree (id)
orders_legacy_ref_idx	120 MB	0		CREATE INDEX orders_legacy_ref_idx ON public.orders USING btree (legacy_ref)

🔧 Engineer Workstation

Status

① Fetched — Notes

Remarks / Observations

Suggestions / Actions Taken

Observed Exec Time
before analysis (ms)

Actual execution time you observed when running the query in the terminal before any tuning.

# -3391204818210037571

🔍 Visual Plan

Count

9800

Avg ms

1340.52

Total

13137096.00

Updated

All Columns

count

9800

average

1340.52

total

13137096.00

rows_returned

50

min_ms

402.16

max_ms

8177.17

stddev_ms

1072.42

cache_hit_pct

38.40

blocks_read

5100000

temp_blocks_written

4200

role_name

app_rw

Query with Placeholders

SELECT o.id, o.total_cents, c.email FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.created_at > $1 ORDER BY o.created_at OFFSET $2 LIMIT 50

Placeholder Values (0)

#	Value
No placeholders

Full Resolved Query✎ editable

Query Execution Result(EXPLAIN ANALYZE JSON — from --run_queries)

[
  {
    "Plan": {
      "Node Type": "Limit",
      "Startup Cost": 121870.2,
      "Total Cost": 121870.9,
      "Plan Rows": 50,
      "Plan Width": 48,
      "Actual Startup Time": 1338.0,
      "Actual Total Time": 1338.2,
      "Actual Rows": 50,
      "Actual Loops": 1,
      "Plans": [
        {
          "Node Type": "Sort",
          "Startup Cost": 119120.1,
          "Total Cost": 125870.3,
          "Plan Rows": 2700000,
          "Plan Width": 48,
          "Actual Startup Time": 1301.5,
          "Actual Total Time": 1337.9,
          "Actual Rows": 400050,
          "Actual Loops": 1,
          "Sort Key": [
            "o.created_at"
          ],
          "Sort Method": "external merge",
          "Sort Space Used": 148320,
          "Sort Space Type": "Disk",
          "Plans": [
            {
              "Node Type": "Hash Join",
              "Join Type": "Inner",
              "Startup Cost": 41210.0,
              "Total Cost": 83120.0,
              "Plan Rows": 2700000,
              "Plan Width": 48,
              "Actual Startup Time": 210.2,
              "Actual Total Time": 980.4,
              "Actual Rows": 2704411,
              "Actual Loops": 1,
              "Hash Cond": "(o.customer_id = c.id)",
              "Plans": [
                {
                  "Node Type": "Seq Scan",
                  "Parallel Aware": false,
                  "Relation Name": "orders",
                  "Schema": "public",
                  "Alias": "o",
                  "Total Cost": 98201.0,
                  "Plan Rows": 2700000,
                  "Actual Rows": 2704411,
                  "Actual Loops": 1,
                  "Actual Total Time": 520.1,
                  "Filter": "(created_at > $1)",
                  "Rows Removed by Filter": 1495589
                },
                {
                  "Node Type": "Hash",
                  "Total Cost": 31210.0,
                  "Plan Rows": 1100000,
                  "Actual Rows": 1100000,
                  "Actual Loops": 1,
                  "Actual Total Time": 205.3,
                  "Plans": [
                    {
                      "Node Type": "Seq Scan",
                      "Parallel Aware": false,
                      "Relation Name": "customers",
                      "Schema": "public",
                      "Alias": "c",
                      "Total Cost": 31210.0,
                      "Plan Rows": 1100000,
                      "Actual Rows": 1100000,
                      "Actual Loops": 1,
                      "Actual Total Time": 120.8
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    },
    "Planning Time": 0.9,
    "Triggers": [],
    "Execution Time": 1339.6
  }
]

📊 Table Intelligence(2 table(s) — stats, indexes, dead tuples, vacuum/analyze history)

📌 customers2 index(es)

Dead Tuples %

0.9

Dead Tuple Size

?

Live Tuples

1100000

Dead Tuples

9800

Total Size

410 MB

Table Size

?

Index Size

?

Mods Since Analyze

40000

Seq Scans

9800

Last Analyze

?

Last Autoanalyze

9 days ago

Last Vacuum

?

Last Autovacuum

2 days ago

Indexes

Index Name	Size	Scans	Tuples Read	Definition
PK customers_pkey	120 MB	2400000		CREATE INDEX customers_pkey ON public.customers USING btree (id)
customers_email_key	120 MB	410000		CREATE INDEX customers_email_key ON public.customers USING btree (email)

📌 orders2 index(es)

Dead Tuples %

11.8

Dead Tuple Size

?

Live Tuples

4200000

Dead Tuples

560000

Total Size

3.1 GB

Table Size

?

Index Size

?

Mods Since Analyze

2100000

Seq Scans

58010

Last Analyze

?

Last Autoanalyze

9 days ago

Last Vacuum

?

Last Autovacuum

2 days ago

Indexes

Index Name	Size	Scans	Tuples Read	Definition
PK orders_pkey	120 MB	910000		CREATE INDEX orders_pkey ON public.orders USING btree (id)
orders_legacy_ref_idx	120 MB	0		CREATE INDEX orders_legacy_ref_idx ON public.orders USING btree (legacy_ref)

🔧 Engineer Workstation

Status

① Fetched — Notes

Remarks / Observations

Suggestions / Actions Taken

Observed Exec Time
before analysis (ms)

Actual execution time you observed when running the query in the terminal before any tuning.

# 6120389113987650181

🔍 Visual Plan

Count

3120

Avg ms

890.07

Total

2777018.40

Updated

All Columns

count

3120

average

890.07

total

2777018.40

rows_returned

24

min_ms

267.02

max_ms

5429.43

stddev_ms

712.06

cache_hit_pct

44.90

blocks_read

1400000

temp_blocks_written

0

role_name

app_ro

Query with Placeholders

SELECT p.id, p.name, p.price_cents FROM products p WHERE lower(p.name) LIKE $1

Placeholder Values (0)

#	Value
No placeholders

Full Resolved Query✎ editable

Query Execution Result(EXPLAIN ANALYZE JSON — from --run_queries)

[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Relation Name": "products",
      "Schema": "public",
      "Alias": "p",
      "Startup Cost": 0.0,
      "Total Cost": 6120.0,
      "Plan Rows": 900,
      "Plan Width": 52,
      "Actual Startup Time": 12.4,
      "Actual Total Time": 888.1,
      "Actual Rows": 24,
      "Actual Loops": 1,
      "Filter": "(lower((p.name)::text) ~~ $1)",
      "Rows Removed by Filter": 179976,
      "Shared Hit Blocks": 2210,
      "Shared Read Blocks": 1800
    },
    "Planning Time": 0.2,
    "Triggers": [],
    "Execution Time": 889.0
  }
]

📊 Table Intelligence(1 table(s) — stats, indexes, dead tuples, vacuum/analyze history)

📌 products1 index(es)

Dead Tuples %

0.7

Dead Tuple Size

?

Live Tuples

180000

Dead Tuples

1200

Total Size

96 MB

Table Size

?

Index Size

?

Mods Since Analyze

800

Seq Scans

3120

Last Analyze

?

Last Autoanalyze

9 days ago

Last Vacuum

?

Last Autovacuum

2 days ago

Indexes

Index Name	Size	Scans	Tuples Read	Definition
PK products_pkey	120 MB	51000		CREATE INDEX products_pkey ON public.products USING btree (id)

🔧 Engineer Workstation

Status

① Fetched — Notes

Remarks / Observations

Suggestions / Actions Taken

Observed Exec Time
before analysis (ms)

Actual execution time you observed when running the query in the terminal before any tuning.

# 2244509867001129804

🔍 Visual Plan

Count

5400

Avg ms

640.33

Total

3457782.00

Updated

All Columns

count

5400

average

640.33

total

3457782.00

rows_returned

180

min_ms

192.10

max_ms

3906.01

stddev_ms

512.26

cache_hit_pct

72.50

blocks_read

880000

temp_blocks_written

0

role_name

billing

Query with Placeholders

SELECT * FROM invoices WHERE customer_id IN ($1, $2, $3, $4, $5, $6, $7, $8) AND paid = false

Placeholder Values (0)

#	Value
No placeholders

Full Resolved Query✎ editable

Query Execution Result(EXPLAIN ANALYZE JSON — from --run_queries)

[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Relation Name": "invoices",
      "Schema": "public",
      "Alias": "invoices",
      "Startup Cost": 0.0,
      "Total Cost": 81200.0,
      "Plan Rows": 1200,
      "Plan Width": 140,
      "Actual Startup Time": 3.1,
      "Actual Total Time": 638.2,
      "Actual Rows": 180,
      "Actual Loops": 1,
      "Filter": "((NOT paid) AND (customer_id = ANY (ARRAY[$1, $2, $3, $4, $5, $6, $7, $8])))",
      "Rows Removed by Filter": 3799820
    },
    "Planning Time": 0.4,
    "Triggers": [],
    "Execution Time": 639.1
  }
]

📊 Table Intelligence(1 table(s) — stats, indexes, dead tuples, vacuum/analyze history)

📌 invoices2 index(es)

Dead Tuples %

3.1

Dead Tuple Size

?

Live Tuples

3800000

Dead Tuples

120000

Total Size

2.2 GB

Table Size

?

Index Size

?

Mods Since Analyze

300000

Seq Scans

5400

Last Analyze

?

Last Autoanalyze

9 days ago

Last Vacuum

?

Last Autovacuum

2 days ago

Indexes

Index Name	Size	Scans	Tuples Read	Definition
PK invoices_pkey	120 MB	88000		CREATE INDEX invoices_pkey ON public.invoices USING btree (id)
invoices_number_idx	120 MB	0		CREATE INDEX invoices_number_idx ON public.invoices USING btree (number)

🔧 Engineer Workstation

Status

① Fetched — Notes

Remarks / Observations

Suggestions / Actions Taken

Observed Exec Time
before analysis (ms)

Actual execution time you observed when running the query in the terminal before any tuning.

# -9016553362119045720

🔍 Visual Plan

Count

120000

Avg ms

220.41

Total

26449200.00

Updated

All Columns

count

120000

average

220.41

total

26449200.00

rows_returned

1

min_ms

66.12

max_ms

1344.50

stddev_ms

176.33

cache_hit_pct

91.30

blocks_read

310000

temp_blocks_written

0

role_name

app_ro

Query with Placeholders

SELECT count(*) FROM events WHERE account_id = $1 AND occurred_at BETWEEN $2 AND $3

Placeholder Values (0)

#	Value
No placeholders

Full Resolved Query✎ editable

Query Execution Result(EXPLAIN ANALYZE JSON — from --run_queries)

[
  {
    "Plan": {
      "Node Type": "Aggregate",
      "Strategy": "Plain",
      "Startup Cost": 40110.0,
      "Total Cost": 40110.1,
      "Plan Rows": 1,
      "Plan Width": 8,
      "Actual Startup Time": 219.0,
      "Actual Total Time": 219.1,
      "Actual Rows": 1,
      "Actual Loops": 1,
      "Plans": [
        {
          "Node Type": "Bitmap Heap Scan",
          "Relation Name": "events",
          "Alias": "events",
          "Startup Cost": 1210.0,
          "Total Cost": 39900.0,
          "Plan Rows": 84000,
          "Plan Width": 0,
          "Actual Startup Time": 21.0,
          "Actual Total Time": 205.7,
          "Actual Rows": 812000,
          "Actual Loops": 1,
          "Recheck Cond": "(account_id = $1)",
          "Filter": "((occurred_at >= $2) AND (occurred_at <= $3))",
          "Rows Removed by Filter": 6100000,
          "Exact Heap Blocks": 12000,
          "Lossy Heap Blocks": 48000,
          "Plans": [
            {
              "Node Type": "Bitmap Index Scan",
              "Index Name": "events_account_id_idx",
              "Total Cost": 1190.0,
              "Plan Rows": 84000,
              "Actual Rows": 6912000,
              "Actual Loops": 1,
              "Actual Total Time": 19.0,
              "Index Cond": "(account_id = $1)"
            }
          ]
        }
      ]
    },
    "Planning Time": 0.3,
    "Triggers": [],
    "Execution Time": 220.0
  }
]

📊 Table Intelligence(1 table(s) — stats, indexes, dead tuples, vacuum/analyze history)

📌 events2 index(es)

Dead Tuples %

3.5

Dead Tuple Size

?

Live Tuples

52000000

Dead Tuples

1900000

Total Size

41 GB

Table Size

?

Index Size

?

Mods Since Analyze

9000000

Seq Scans

12

Last Analyze

?

Last Autoanalyze

9 days ago

Last Vacuum

?

Last Autovacuum

2 days ago

Indexes

Index Name	Size	Scans	Tuples Read	Definition
PK events_pkey	120 MB	100		CREATE INDEX events_pkey ON public.events USING btree (id)
events_account_id_idx	120 MB	6100000		CREATE INDEX events_account_id_idx ON public.events USING btree (account_id)

🔧 Engineer Workstation

Status

① Fetched — Notes

Remarks / Observations

Suggestions / Actions Taken

Observed Exec Time
before analysis (ms)

Actual execution time you observed when running the query in the terminal before any tuning.

# 4498120936541100337

🔍 Visual Plan

Count

250000

Avg ms

95.80

Total

23950000.00

Updated

All Columns

count

250000

average

95.80

total

23950000.00

rows_returned

1

min_ms

28.74

max_ms

584.38

stddev_ms

76.64

cache_hit_pct

88.00

blocks_read

210000

temp_blocks_written

0

role_name

app_rw

Query with Placeholders

UPDATE carts SET updated_at = now() WHERE session_id = $1

Placeholder Values (0)

#	Value
No placeholders

Full Resolved Query✎ editable

Query Execution Result(EXPLAIN ANALYZE JSON — from --run_queries)

[
  {
    "Plan": {
      "Node Type": "ModifyTable",
      "Operation": "Update",
      "Relation Name": "carts",
      "Alias": "carts",
      "Startup Cost": 0.0,
      "Total Cost": 52100.0,
      "Plan Rows": 0,
      "Plan Width": 0,
      "Actual Startup Time": 95.1,
      "Actual Total Time": 95.2,
      "Actual Rows": 0,
      "Actual Loops": 1,
      "Plans": [
        {
          "Node Type": "Seq Scan",
          "Parallel Aware": false,
          "Relation Name": "carts",
          "Schema": "public",
          "Alias": "carts",
          "Total Cost": 52100.0,
          "Plan Rows": 1,
          "Plan Width": 14,
          "Actual Total Time": 94.6,
          "Actual Rows": 1,
          "Actual Loops": 1,
          "Filter": "(session_id = $1)",
          "Rows Removed by Filter": 2299999
        }
      ]
    },
    "Planning Time": 0.2,
    "Triggers": [],
    "Execution Time": 95.6
  }
]

📊 Table Intelligence(1 table(s) — stats, indexes, dead tuples, vacuum/analyze history)

📌 carts1 index(es)

Dead Tuples %

31.1

Dead Tuple Size

?

Live Tuples

2300000

Dead Tuples

1040000

Total Size

1.9 GB

Table Size

?

Index Size

?

Mods Since Analyze

1040000

Seq Scans

250000

Last Analyze

?

Last Autoanalyze

9 days ago

Last Vacuum

?

Last Autovacuum

2 days ago

Indexes

Index Name	Size	Scans	Tuples Read	Definition
PK carts_pkey	120 MB	1200		CREATE INDEX carts_pkey ON public.carts USING btree (id)

🔧 Engineer Workstation

Status

① Fetched — Notes

Remarks / Observations

Suggestions / Actions Taken

Observed Exec Time
before analysis (ms)

Actual execution time you observed when running the query in the terminal before any tuning.