Linked Questions

9 votes
4 answers
25k views

Postgresql extremely slow count (with index, simple query) [duplicate]

I need to run these simple queries on a table with millions of rows: SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123; SELECT COUNT(*) FROM "subscriptions" WHERE "...
collimarco's user avatar
12 votes
1 answer
28k views

Index usage on a temporary table

I have two rather simple queries. The first query UPDATE mp_physical SET periodic_number = '' WHERE periodic_number is NULL; and it's plan duration: 0.125 ms plan: Query Text: UPDATE ...
Artem Zefirov's user avatar
5 votes
2 answers
5k views

Postgres 9.4.4 query takes forever

We're running Postgres 9.4.4 on CentOS 6.5 and have a SELECT query that has worked for years, but stopped working and hangs after we upgraded from 9.2 (it took a while to notice it, so I don't know if ...
user1517922's user avatar
3 votes
1 answer
6k views

Using ILIKE with unaccent and with only right end wildcard

I use Postgresql 9.4 and I have a big table named foo. I want to search on it but I get long execution times if the search text is very short (e.g. "v") or long (e.g. "This is a search example with ...
phlegx's user avatar
  • 133
0 votes
2 answers
3k views

How to speed up single-column date range query in Postgres?

How would you speed up a Postgres query that's trying to filter on a date column between a start and end date? I'm running a query like: SELECT * FROM record WHERE tag_id IN (1,2,3) AND person_id = 1 ...
Cerin's user avatar
  • 1,375
0 votes
2 answers
2k views

How to search a table with 80 million records faster?

I have a table with about 80 million records, I want to find all the activities of lists and workspaces that a user has access to. So first, I get the ids of the lists and workspaces and then I run ...
Alan's user avatar
  • 1
3 votes
2 answers
1k views

Poor SELECT performance on recheck cond with data and large (millions of rows) partial index

Query: EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT (*) AS "count" FROM "Posts" AS "Post" WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398' AND "Post"."new" = TRUE; Index: CREATE INDEX ...
Garrett's user avatar
  • 1,043
5 votes
1 answer
285 views

Non-deterministic performance of query on select, from 1s to 60s on table with 1 billion rows

I'm trying to investigate why the performance of this query is so non-deterministic. It can take anywhere from 1 seconds, to 60 seconds and above. The nature of the query is to select a "time ...
valkmit's user avatar
  • 53
0 votes
1 answer
562 views

How can I speed up my query that involves `max(timestamp)` in a huge table? I already added an index to every field

I have a huge table that has fields ip, mac, and timestamp. Neither of the three fields is unique, but the combination of all three is. Table is automatically populated, with newer records added all ...
Granny Aching's user avatar
2 votes
1 answer
129 views

Is it possible to get a "pages touched" statistic from PostgreSQL?

With PostgreSQL one can measure disk reads and cache hits. Is it possible to have a clearer statistic of how many index pages were "touched" to run a query? If so, how?
audlift-bit's user avatar