Linked Questions
10 questions linked to/from PostgreSQL partial index unused when created on a table with existing data
6
votes
1
answer
9k
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"...
- 176k
2
votes
1
answer
111
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....
CommunityBot
- 1
10
votes
1
answer
4k
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 ...
- 176k
39
votes
3
answers
42k
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 (...
- 176k
9
votes
1
answer
15k
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 (
...
CommunityBot
- 1
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 ...
CommunityBot
- 1
4
votes
1
answer
963
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 ...
CommunityBot
- 1
1
vote
1
answer
255
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 ...
CommunityBot
- 1
1
vote
1
answer
90
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 ;
...
- 31.3k
6
votes
1
answer
3k
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 ...
CommunityBot
- 1