Linked Questions
81 questions linked to/from Configuring PostgreSQL for read performance
138
votes
5
answers
166k
views
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 ...
- 1,655
96
votes
5
answers
43k
views
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 ...
- 2,013
129
votes
3
answers
65k
views
Is a composite index also good for queries on the first field?
Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?
...
- 1,741
42
votes
2
answers
44k
views
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 ...
- 721
54
votes
1
answer
58k
views
Indexes: integer vs string performance if the number of nodes is the same
I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is ...
- 643
36
votes
2
answers
85k
views
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 ...
- 957
23
votes
2
answers
23k
views
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, ...
- 353
25
votes
1
answer
21k
views
What is the overhead for varchar(n)?
I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type:
The storage requirement for a short string (up to 126 bytes) is 1 byte
plus the actual string, which ...
- 353
15
votes
4
answers
35k
views
Slow query on large table with GROUP BY and ORDER BY
I have a table with 7.2 million tuples which looks like this:
table public.methods
column | type | attributes
--------+-----------...
- 299
14
votes
2
answers
17k
views
Using PostgreSQL what is the difference between a smallint and a bool for storing boolean?
What is the difference between the smallint type and the bool type for storing boolean values?
This question arose in the comments to a question on Geographic Information Systems Stack Exchange.
- 64.2k
14
votes
1
answer
7k
views
How do completely empty columns in a large table affect performance?
I have 400 million rows in a Postgres db, and the table has 18 columns:
id serial NOT NULL,
a integer,
b integer,
c integer,
d smallint,
e timestamp without time zone,
f smallint,
g timestamp without ...
- 243
16
votes
1
answer
62k
views
Optimizing large database query (25+ million rows, using max() and GROUP BY)
I'm using Postgres 9.3.5 and I have a large table in the database, currently it has over 25 million rows and it tends to get even larger rapidly. I'm trying to select specific rows (all unit_ids with ...
- 285
3
votes
2
answers
20k
views
Very slow execution of a simple query
I try to create a report for my data, but it is really slow on a big table.
The table structure is:
CREATE TABLE posts
(
id serial NOT NULL,
project_id integer,
moderation character varying(...
8
votes
1
answer
5k
views
Index for large bit strings
I'm trying to extend PostgreSQL to index bit strings up to 1000 bits. (These bit strings are created by quantization of high-dimensional vectors, so for each dimension up to 4 bits are assigned). ...
- 649
13
votes
2
answers
6k
views
Does it make sense to store a couple of Boolean values as array?
I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.)
Instead of having Boolean columns, I could have a single array ...
- 233