---
title: "Slow Database Queries? Are Database Types to Blame?"
description: "Slow database queries can stem from schema choices as well as inefficient SQL. The example uses PostgreSQL with a json column holding large payloads, then selects a single url value for an app_uuid and orders the results. EXPLAIN ANALYZE with BUFFERS showed 7,106 shared buffer hits, representing about 60MB of JSON data loaded and discarded, while execution reached 1,141.296 milliseconds in the reported run. The solution split the JSON into separate text fields for data and url, bringing query time down from about 1,200 milliseconds to 10 milliseconds. The broader conclusion is that schemas should be revisited as application usage becomes better understood, but changes are not necessary when the design isn't causing a problem."
---

# Slow Database Queries? Are Database Types to Blame?

[Heroku](https://yomu.fyi/company/heroku) · Ben Fritsch · Dec 19, 2019

**Type:** Problem & solution

## Summary

Slow database queries can stem from schema choices as well as inefficient SQL. The example uses PostgreSQL with a json column holding large payloads, then selects a single url value for an app\_uuid and orders the results. EXPLAIN ANALYZE with BUFFERS showed 7,106 shared buffer hits, representing about 60MB of JSON data loaded and discarded, while execution reached 1,141.296 milliseconds in the reported run. The solution split the JSON into separate text fields for data and url, bringing query time down from about 1,200 milliseconds to 10 milliseconds. The broader conclusion is that schemas should be revisited as application usage becomes better understood, but changes are not necessary when the design isn't causing a problem.

## Context

A PostgreSQL query retrieving url values from large JSON payloads had an average execution time of 10ms but outliers as high as 1200ms. Standard remedies such as reducing locks, adding indexes, or rewriting queries did not address the underlying schema-related cost. EXPLAIN (ANALYZE, BUFFERS) showed that the query loaded and discarded about 60MB of JSON data to retrieve the needed values.

## Approach / What changed

The JSON column was replaced with two separate text fields, one for data and one for url. This allowed the query to scope its work to the specific information required, reducing the query time from about 1200ms to 10ms.

## Takeaways

- EXPLAIN (ANALYZE, BUFFERS) reports PostgreSQL I/O details, including whether data comes from cache or disk and the number of shared buffer hits.
- The reported query used 7,106 8KB PostgreSQL blocks, or about 60MB of JSON data, despite returning only the requested url values.
- The authors recommend auditing schema data types as application usage evolves, while avoiding changes solely for the sake of changing tables.

**Tags:** [Performance](https://yomu.fyi/topic/performance), [Postgres](https://yomu.fyi/topic/postgres), [SQL](https://yomu.fyi/topic/sql)

- Source: [Heroku](https://www.heroku.com/blog/know-your-database-types)
- Source URL: https://www.heroku.com/blog/know-your-database-types
- Ingested by Yomu: 2026-09-09T16:03:01.121Z

[Read original post](https://www.heroku.com/blog/know-your-database-types)