Linked Questions

39 votes
3 answers
39k views

Are regular VACUUM ANALYZE still recommended under 9.1?

I'm using PostgreSQL 9.1 on Ubuntu. Are scheduled VACUUM ANALYZE still recommended, or is autovacuum enough to take care of all needs? If the answer is "it depends", then: I have a largish database (...
user avatar
9 votes
1 answer
10k views

Optimise a LATERAL JOIN query on a big table

I'm using Postgres 9.5. I have a table that records page hits from several web sites. This table contains about 32 million rows spanning from Jan 1, 2016 to June 30, 2016. CREATE TABLE event_pg ( ...
user avatar
4 votes
1 answer
5k views

Postgres partial index on IS NULL not working

Postgres version Using PostgreSQL 10.3. Table definition CREATE TABLE tickets ( id bigserial primary key, state character varying, closed timestamp ); CREATE INDEX "state_index" ON "tickets"...
user avatar
7 votes
1 answer
2k views

Inconsistent statistics on jsonb column with btree index

I noticed that the performance of a query involving a jsonb column varied significantly between VACUUM ANALYZE runs while testing it. I get completely different execution plans seemingly randomly ...
user avatar
9 votes
1 answer
2k views

Best index for column where values are mostly the same

We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to ...
user avatar
  • 231
6 votes
1 answer
2k views

Index is not used with table inheritance

I have a PostgreSQL 9.0.12 database with a master table and 2 child tables. My tables: CREATE TABLE test2 ( id serial PRIMARY KEY, coll character varying(15), ts timestamp without time ...
user avatar
  • 275
3 votes
1 answer
608 views

Multi-expression indexes in PostgreSQL not using being used as expected

I have a PostgreSQL 9.3 table with multiple expression indexes defined. When I use explain to see which indexes are being used for my query, I am surprised to see that PostgreSQL is not using the ...
user avatar
1 vote
1 answer
87 views

Strategies for reducing data to summaries within a table over time?

I have a large data set where large numbers of entries differ only by timestamp. In the short term it's beneficial to maintain individual entries so that we can correlate by time for individual data ...
user avatar
  • 187
1 vote
1 answer
84 views

ORDER BY text doesn't use my index?!

currently this query doesn't use a index even after I created one: EXPLAIN ANALYZE SELECT * FROM customer_list WHERE rating = 3 ORDER BY name ASC LIMIT 20 ; ...
user avatar
2 votes
1 answer
103 views

Improving performance of count where only result > 0 matters

I have following query: select count(*) -- rp.tag from submission inner join post rp on submission.id = rp.submission_id where user_name = ?1 and lower(rp.tag) like ('%verified%') and not lower(rp....
user avatar
  • 125