Linked Questions

2 votes
1 answer

Is there a possible reason for having index on (a) and on (a,b) at the same time [duplicate]

Suppose I have a table with several column, among them - columns a and b. Is there any use case for which having both multi-column index on (a,b) and a single-column index on (a) is beneficial? ...
Ivan Sopov's user avatar
137 votes
5 answers

Measure the size of a PostgreSQL table row

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
Joe's user avatar
  • 1,635
95 votes
5 answers

Working of indexes in PostgreSQL

I have a couple of questions regarding working of indexes in PostgreSQL. I have a Friends table with the following index: Friends ( user_id1 ,user_id2) user_id1 and user_id2 are foreign keys to ...
codecool's user avatar
  • 1,983
49 votes
2 answers

Configuring PostgreSQL for read performance

Our system writes a lots of data (kind of Big Data system). Write performance is good enough for our needs but read performance is really too slow. The primary key (constraint) structure is similar ...
JPelletier's user avatar
50 votes
3 answers

How do I know what indexes to create for a table?

Is there a way I can figure out the best way to know which indexes to create for a table?
Nick Ginanto's user avatar
42 votes
2 answers

Multicolumn index and performance

I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries. The scenario: PostgreSQL 8.4, table with about one ...
jap1968's user avatar
  • 721
35 votes
2 answers

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
tomka's user avatar
  • 927
31 votes
3 answers

Constraint to enforce "at least one" or "exactly one" in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | email_address | ...
Kevin Burke's user avatar
23 votes
2 answers

Many columns vs few tables - performance wise

Yes, I am aware that data normalization should be my priority (as it is). I've got a table with 65 columns storing vehicle data with columns: used_vehicle, color, doors, mileage, price and so forth, ...
Urim Kurtishi's user avatar
26 votes
2 answers

Slow index scans in large table

Update 2020-08-04: Since this answer is apparently still being viewed regularly I wanted to provide an update on the situation. We're currently using PG 11 with table partitioning on timestamp and are ...
Exelian's user avatar
  • 429
19 votes
2 answers

Do covering indexes in PostgreSQL help JOIN columns?

I have a whole lot of tables that look vaguely like this: CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER); CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES ...
ldrg's user avatar
  • 679
11 votes
3 answers

How to avoid a cyclic dependency (circular reference) between 3 tables?

I have 3 tables: People Post Likes When I design the ER model it has a cyclic dependency: 1:N People --------< Post 1:N Post ----------< Likes 1:N People -----...
Ragu's user avatar
  • 211
15 votes
2 answers

PostgreSQL Recursive Descendant Depth

I need to calculate the depth of a descendant from it's ancestor. When a record has object_id = parent_id = ancestor_id, it is considered a root node (the ancestor). I have been trying to get a WITH ...
Diggity's user avatar
  • 151
19 votes
1 answer

How to index a query with `WHERE field IS NULL`?

I have a table with lots of inserts, setting one of the fields (uploaded_at) to NULL. Then a periodic task selects all the tuples WHERE uploaded_at IS NULL, processes them and updates, setting ...
Kirill Zaitsev's user avatar
9 votes
1 answer

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 ( ...
maxTrialfire's user avatar
  • 1,084

15 30 50 per page
2 3 4 5 6