slow postgres performance with simple querys
-3
votes
1
answer
371
views
After a lot of reading I found multiple sources saying Postgres should be able to handle ~100 million rows.
I set up a simple table with:
CREATE TABLE trade
(
id INT PRIMARY KEY NOT NULL,
symbol VARCHAR(),
open_time TIMESTAMP,
end_price INT
)
I have 12 million of these records.
My simple query of
SELECT * FROM table WHERE symbol=x and open_time>start_time and open_time
This query always returns less than a 1000 rows yet it takes 1100 milliseconds. This seems like a lot for a simple table with 10x less rows than it should handle?
Although I don't have any indexes cause I don't know what would be the best indexes to put on this table.
Is it possible to get this down to 100ms?
Any help writing a more performant query or db would be appreciated
EDIT 1:
After reading the comments I put the following indexes on the table
CREATE INDEX open_time ON trade(open_time);
CREATE INDEX symbol ON trade(symbol);
CREATE INDEX end_price ON trade(open_price);
After adding this the query time is 240 ms is this the max?
Although I have noticed
querying the beginning 40k rows the query time drops to 60ms
after that is rises to 240ms what is causing this?
Asked by sir snibbles
(11 rep)
May 19, 2022, 07:53 PM
Last activity: Jul 9, 2025, 02:43 PM
Last activity: Jul 9, 2025, 02:43 PM