How to optimize the simplest SQL query of a very simple table?

All we need is an easy explanation of the problem, so here it is.

Query

SELECT * FROM public.data

Table

  • id → PK Integer
  • data → json
  • timestamp → timestamp
  • version → integer

The data column can have "large" values (e.g ~250kb)

Result of query

  • Time: 4m 52s
  • Nb rows: 620

I’m not sure what’s the bottleneck here. It seems like it shouldn’t take that much time to fetch only 620 rows. Perhaps the data column is what’s slowing everything down?

Is there something I could do to make this query much faster? I tried using an index, but no success. As you can tell I’m no DB expert and maybe there’s a low-hanging fruit solution since this is a very basic query.

Thanks for the help 😊


Edit 1

Here’s the output of explain (analyze, buffers) select * from public.data;

"Seq Scan on data  (cost=0.00..7.86 rows=619 width=34) (actual time=0.007..0.083 rows=620 loops=1)"
"  Buffers: shared hit=6"
"Planning Time: 0.171 ms"
"Execution Time: 0.138 ms"

Here’s the output of the create table for this table:

-- Table: public.data

-- DROP TABLE IF EXISTS public.data;

CREATE TABLE IF NOT EXISTS public.data
(
    id integer NOT NULL DEFAULT nextval('data_id_seq'::regclass),
    data json NOT NULL,
    "timestamp" timestamp with time zone NOT NULL,
    version integer NOT NULL,
    CONSTRAINT data_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.data
    OWNER to vxpxchlkkyyvaa;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I discovered that the issue only occurred locally.

I assume that my Heroku dyno and my PG DB are in the same AWS region and have some sort of prioritized access to each other which makes the communication really fast. Indeed, on my server, it takes less than a second to make the query, whereas when I’m making the query locally (either using pgAdmin4 or programmatically), it takes ~5m.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply