Questions about PostgreSQL query optimisation. In your question please include the full original text of SQL queries, `EXPLAIN(BUFFERS, ANALYZE)` for those queries, and `CREATE TABLE` and `CREATE INDEX` statements or `psql` `\dt+ tablename` output. If you're looking to tune without modifying ...
5
votes
3answers
227 views
Any downsides of using data type “text” for storing strings?
As per Postgres documentation, they support 3 data-types for character data:
character varying(n), varchar(n) variable-length with limit
character(n), char(n) fixed-length, blank ...
8
votes
6answers
3k views
Best way to delete millions of rows by ID
I need to delete about 2 million rows from my PG database. I have a list of IDs that I need to delete. However, any way I try to do this is taking days.
I tried putting them in a table and doing it ...
3
votes
5answers
131 views
Way to try multiple SELECTs till a result is available?
What if I want to search for a single row in a table with a decrementing precision, e.g. like this:
SELECT * FROM image WHERE name LIKE 'text' AND group_id = 10 LIMIT 1
When this gives me no ...
1
vote
2answers
199 views
Optimizing GROUP BY SQL query to retrieve latest record
I have the following table (simplified form) in Postgres 9.2
CREATE TABLE user_msg_log (
aggr_date DATE,
user_id INTEGER,
running_total INTEGER
);
It contains up to one record per user ...
13
votes
3answers
5k views
PostgreSQL query runs faster with index scan, but engine chooses hash join
The query:
SELECT "replays_game".*
FROM "replays_game"
INNER JOIN
"replays_playeringame" ON "replays_game"."id" = "replays_playeringame"."game_id"
WHERE "replays_playeringame"."player_id" = 50027
...
6
votes
2answers
3k views
PostgreSQL query taking too long
I have database with few hundred millions of rows. I'm running the following query:
select * from "Payments" as p
inner join "PaymentOrders" as po
on po."Id" = p."PaymentOrderId"
inner join "Users" ...
6
votes
3answers
1k views
Optimization of count query for PostgreSQL
I have a table in postgresql that contains an array which is updated constantly.
In my application i need to get the number of rows for which a specific parameter is not present in that array column. ...
5
votes
1answer
5k views
Optimize Postgres timestamp query range
I have the following table and indices defined:
CREATE TABLE ticket
(
wid bigint NOT NULL DEFAULT nextval('tickets_id_seq'::regclass),
eid bigint,
created timestamp with time zone NOT NULL ...
3
votes
2answers
1k views
Efficiently querying a huge time series table for one row every 15 minutes
I have two tables, conttagtable (t) and contfloattable (cf). T has about 43k rows. CF has over 9 billion.
I created an index on both tables on the tagindex column on both tables. This column can ...
1
vote
2answers
546 views
Bitmap Heap Scan performance
I have a big report table. Bitmap Heap Scan step take more than 5 sec.
Is there something that I can do? I add columns to the table, does reindex the index that it use will help?
I do union and sum ...
6
votes
1answer
128 views
Indexed ORDER BY with LIMIT 1
I'm trying to fetch most recent row in a table. I have a simple timestamp created_at which is indexed. When I query ORDER BY created_at DESC LIMIT 1, it takes far more than I think it should (about ...
2
votes
3answers
136 views
Why does the following join increase the query time significantly?
I have a star schema here and I am querying the fact table and would like to join one very small dimension table. I can't really explain the following:
EXPLAIN ANALYZE SELECT
COUNT(impression_id), ...
7
votes
3answers
6k views
Improving query speed: simple SELECT in big postgres table
I'm having trouble regarding speed in a SELECT query on a Postgres database.
I have a table with two integer columns as key: (int1,int2)
This table has around 70 million rows.
I need to make two ...
5
votes
2answers
184 views
Faster search for records where 1st character of field doesn't match [A-Za-z]?
I currently have the following:
User (id, fname, lname, deleted_at, guest)
I can query for a list of user's by their fname initial like so:
User Load (9.6ms) SELECT "users".* FROM "users" WHERE ...
3
votes
4answers
158 views
Order BY turns a 30ms query into a 7120ms query. Known performance issue?
I have a User table with 1m records:
User (id, fname, lname, deleted_at, guest)
I have the following query which is being run against a postgres 9.1 db:
SELECT "users".*
FROM "users"
WHERE ...
2
votes
1answer
532 views
PostgreSQL window function: partition by comparison
I'm trying to find the way of doing a comparison with the current row in the PARTITION BY clause in a WINDOW function in PostgreSQL query.
Imagine I have the short list in the following query of this ...
2
votes
1answer
181 views
Slow postgres query with multiple joins to same table
The following query is running very slowly for me:
SELECT r.comp,b.comp,n.comp
FROM paths AS p
INNER JOIN comps AS r ON (p.root=r.id)
INNER JOIN comps AS b ON (p.base=b.id)
INNER JOIN comps ...
1
vote
1answer
64 views
Reuse computed select value
I'm trying to use ST_SnapToGrid and then GROUP BY the grid cells (x, y). Here is what I did first:
SELECT
COUNT(*) AS n,
ST_X(ST_SnapToGrid(geom, 50)) AS x,
...
1
vote
4answers
114 views
Postgres NOT IN performance
Hi any ideas how to speed up this query?
Input
EXPLAIN SELECT entityid FROM entity e
LEFT JOIN level1entity l1 ON l.level1id = e.level1_level1id
LEFT JOIN level2entity l2 ON l2.level2id = ...
