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,631
29 votes
4 answers

Index performance for CHAR vs VARCHAR (Postgres)

In this answer ( a single remark caught my eye: Also keep in mind that there's often a very big difference between ...
LetMeSOThat4U's user avatar
44 votes
1 answer

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There ...
Bohemian's user avatar
  • 683
18 votes
1 answer

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

I have a table which is created this way: -- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) ...
Valentin Despa's user avatar
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
5 votes
1 answer

Improve UPDATE performance on big table

I'm using Postgres 9.5 on Amazon RDS (2vCPU, 8 GB RAM). I use pganalyze to monitor my performance. I have around 200K records in the database. In my Dashboard I see the following queries are taking ...
californian's user avatar
8 votes
1 answer

Do datatypes of columns affect query performance?

Let's suppose I have this table: Table "public.orders" Column | Type | Collation | Nullable | Default -----------------+---------------+-----------+----------+----...
tail's user avatar
  • 235
1 vote
2 answers

varchar(n) size?

When I use varchar(5) in a INSERT query it means that the attribute in the table will take exactly 5 bytes in memory? (Given that one printable character takes one byte)?
d4bbi's user avatar
  • 35
5 votes
2 answers

How to make this query use my multicolumn index?

Currently, I have a view which is defined like this: View "public.customer_list" Column | Type | Modifiers | Storage | Description -----------+---------...
Christian Schmitt's user avatar
5 votes
1 answer

Is varchar(x) as fast as `text CHECK ( char_length(x) )`?

In an Twitter exchange, Simon West asks to Brandur, Out of interest, why do use email TEXT CHECK (char_length(email) <= 255) rather than email VARCHAR(255)? Not a pattern I've seen before ...
Evan Carroll's user avatar
  • 62.2k
4 votes
2 answers

Compound Index on 43 Million PostgreSQL table

This question is related to a prior one of I asked: Order of columns in a compound index in PostgreSQL (and query order) Rather than overload that question, I figure I can sharpen and limit my ...
Dr.YSG's user avatar
  • 389
3 votes
3 answers

Impact of index on a "status" field with one (guaranteed) change

Introduction I have a PostgreSQL table setup as a queue/event-source. I would very much like to keep the "order" of the events (even after the queue item has been processed) as a source for ...
WesAtWork's user avatar
2 votes
1 answer

Sensible max_length for Postgres db charfields

I am using Django with a Postgres backend. In the Django models, I have to set the max_length attribute on the Charfields, then the Django ORM deals with the database in the backend. In a lot of cases,...
logicOnAbstractions's user avatar
1 vote
3 answers

How to map similar camelcased, pascalcased, underscored, lowerscored words to the same id?

I'm working on PostgreSQL and I have a table like this called words_table: I need all equivalent words (like software_design and software-design) to have its map value = to its word's equivalent ...
Tiago Stapenhorst's user avatar
1 vote
1 answer

RIGHT() function in postgresql 9.1 not behaving as anticipated?

The following is with regard to PostgresQL 9.1 : I am using the right() and left() string functions to fix a few instances where data was input into my database being “off by one” .. so I’m doing ...
Joishi Bodio's user avatar
  • 3,318

15 30 50 per page