Linked Questions

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
124 votes
3 answers

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? ...
Luciano's user avatar
  • 1,671
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
54 votes
1 answer

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 ...
Chris Cirefice's user avatar
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
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
25 votes
1 answer

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 ...
keypress's user avatar
  • 353
15 votes
4 answers

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 --------+-----------...
reox's user avatar
  • 299
14 votes
2 answers

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.
Evan Carroll's user avatar
  • 62.7k
16 votes
1 answer

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 ...
errata's user avatar
  • 285
14 votes
1 answer

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 ...
ebi's user avatar
  • 243
3 votes
2 answers

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(...
Leonid Batizhevsky's user avatar
8 votes
1 answer

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). ...
navige's user avatar
  • 599
13 votes
2 answers

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 ...
Xodarap's user avatar
  • 233

15 30 50 per page
2 3 4 5 6