106
Q2: way to measure page size
PostgreSQL provides a number of Database Object Size Functions. I packed the most interesting ones in this query and added some Statistics Access Functions at the bottom. (The additional module pgstattuple provides more useful functions, yet.)
This is going to show that different methods to measure the "size of a row" lead to ...
72
To elaborate on @alci's answer:
PostgreSQL doesn't care what order you write things in
PostgreSQL doesn't care at all about the order of entries in a WHERE clause, and chooses indexes and execution order based on cost and selectivity estimation alone.
The order in which joins are written is also ignored up to the configured join_collapse_limit; if there ...
43
An approximation of the size of a row, including the TOAST'ed contents, is easy to get by querying the length of the TEXT representation of the entire row:
SELECT octet_length(t.*::text) FROM tablename AS t WHERE primary_key=:value;
This is a close approximation to the number of bytes that will be retrieved client-side when executing:
SELECT * FROM ...
39
Due to the MVCC model of Postgres, and according to the rules of SQL, an UPDATE writes a new row version for every row that is not excluded in the WHERE clause.
This does have a more or less substantial impact on performance, directly and indirectly. "Empty updates" have the same cost per row as any other update. They fire triggers (if present) like any ...
36
Instead of using a huge IN-list, join on a VALUES expression, or if the list is large enough, use a temp table, index it, then join on it.
It'd be nice if PostgreSQL could do this internally & automatically but at this point the planner doesn't know how.
Similar topics:
https://stackoverflow.com/q/24647503/398670
https://stackoverflow.com/q/17813492/...
31
First off, can it be? You write:
I want to fetch all data with the updated_at field with a date of a
few days ago.
But your WHERE condition is:
(date(updated_at)) < (date(now())-7)
Shouldn't that be >?
Indexes
For optimal performance, you could ...
partition your indexes
exclude irrelevant rows from the indexes
automatically recreate indexes ...
29
There are actually two different variants of the IN construct in Postgres. One works with a subquery expression (returning a set), the other one with a list of values, which is just shorthand for
expression = value1
OR
expression = value2
OR
...
You are using the second form, which is fine for a short list, but much slower for long lists. Provide your ...
24
Update: Tested all 5 queries in SQLfiddle with 100K rows (and 2 separate cases, one with few (25) distinct values and another with lots (around 25K values).
A very simple query would be to use UNION DISTINCT. I think it would be most efficient if there is a separate index on each of the four columns It would be efficient with a separate index on each of the ...
22
You were close. Your last idea is actually the way to go:
log_statement = none
log_min_duration_statement = 10000
Then no statement will be logged, except those running longer than 10 seconds - including the query string itself. Logging may have seemed to stop because 10 seconds is a high threshold. I am using 2 seconds normally, but YMMV.
This related ...
17
In a search, I would like to get all the rows that exactly match the
bit string.
Use a B-Tree index, the default type. I don't see a case for a GIN index here.
Up to 1000 bits result in up to 133 bytes (or slightly more) storage size on disk for a bit varying type.
SELECT pg_column_size(repeat('1', 1000)::varbit) -- 133
Not that much. A plain B-Tree ...
17
SQL is a declarative language: you tell what you want, not how to do it. The RDBMS will choose the way it will execute the query, called the execution plan.
Once upon a time (5-10 years ago), the way a query was written had a direct impact on the execution plan, but nowadays, most SQL database engines use a Cost Based Optimizer for planning. That is, it ...
17
To avoid flame war, I'll just glance the way each storage work on querying, not really a benchmark. I'll use this table as reference (the code should be slightly modified to run on both RDBMS):
CREATE TABLE employees (
emp_id int,
name varchar,
depto_no int,
salary decimal,
CONSTRAINT emp_pk PRIMARY KEY (emp_id);
);
CREATE INDEX ...
15
tl;dr: The first process that reads data after it is committed will set hint bits. That will dirty the page, creating write activity. The other thing VACUUM (but not other commands) does is marks the page as all-visible, if appropriate. VACUUM will eventually have to hit the table to freeze the tuples.
The work that needs to be done after an insert isn't ...
14
There are a few things that could be happening. In general, I doubt that length is the proximal problem. I suspect instead you have a length-related problem.
You say the text fields can get up to a few k. A row cannot go over 8k in main storage, and it is likely that your larger text fields have been TOASTed, or moved out of main storage into an extended ...
14
Very short version: Yes, sometimes.
PostgreSQL can use bitmap index scans to combine multiple indexes.
A predicate like
WHERE a > 50 AND a < 50000
is a specialisation of the more general form:
wHERE a > 50 and b < 50000
for a = b.
PostgreSQL can use two indexes here, one for each part of the predicate, and then bitmap AND them. It doesn't ...
14
Assuming we are talking about 1:1 relationships among all tables.
Overall storage is practically always (substantially) cheaper with a single table instead of multiple tables in 1:1 relationship. Each row has 28 bytes of overhead, plus typically a few more bytes for extra padding. And you need to store the PK column with every table. And have a separate (...
answered Apr 29 '15 at 19:59
Erwin Brandstetter
116k16 gold badges260 silver badges396 bronze badges
14
For your kind of pattern matching you best use a trigram index. Read this first:
How is LIKE implemented?
I assume there's a typo in your expression (first_name || '' || last_name), which makes no sense with an empty string, and you really want (first_name || ' ' || last_name) - with a space character.
Assuming that either column can be NULL, you would ...
14
This dump was dumped as individual statements (with pg_dump --inserts)
INSERT INTO esa2010_codes VALUES (11002, 'Národn
INSERT INTO esa2010_codes VALUES (11003, 'Nefina
INSERT INTO esa2010_codes VALUES (12502, 'Národn
INSERT INTO esa2010_codes VALUES (11001, 'Verejn
INSERT INTO esa2010_codes VALUES (12602, 'Národn
INSERT INTO esa2010_codes VALUES (12603, '...
12
Since the columns e, k, and n can be NULL, I assume "100% empty" means NULL.
NULL storage is cheap. Each NULL "costs" one bit in the null bitmap for storage and otherwise hardly effects performance. Effective storage requirement depends on whether a null bitmap for each row already exists and has room for 3 more bits.
Tables with up to 8 columns can ...
answered Feb 12 '15 at 21:56
Erwin Brandstetter
116k16 gold badges260 silver badges396 bronze badges
12
You could use LATERAL, like in this query:
SELECT DISTINCT
x.n
FROM
atable
CROSS JOIN LATERAL (
VALUES (a), (b), (c), (d)
) AS x (n)
;
The LATERAL keyword allows the right side of the join to reference objects from the left side. In this case, the right side is a VALUES constructor that builds a single-column subset out of the column values you ...
12
"date"
Don't call your timestamp column "date", that's very misleading. Better yet, don't use the basic type name "date" as identifier at all, that's error-prone, leads to confusing error messages and it's a reserved word in standard SQL. Should be something like:
CREATE TABLE test (
id serial PRIMARY KEY
, ts timestamp NOT NULL -- also adding NOT NULL ...
answered Aug 13 '15 at 17:04
Erwin Brandstetter
116k16 gold badges260 silver badges396 bronze badges
11
Trying to explain why there is difference in performance between the two queries.
This one: SELECT * FROM "items" WHERE "object_id" = '123' LIMIT 1 is satisfied by any one row with the matching object_id, so the index on object_id is a natural choice. The query requires minimal I/O: index scan to find the first matching value plus one heap read to fetch the ...
11
Here is what I do in such cases, usually some of this helps:
Look at the whole query and try to remove unneeded tables from it.
Rethink outer JOINs (that is, LEFT/RIGHT JOIN) and if possible, eliminate them from view definition, replacing by inner JOINS.
Try to increase planner constants so the server can put more effort into planning phase. You can do ...
11
Note: This answer addresses a couple of basic problems, but it's not the final solution. The question was still inconsistent after several requests for clarification, so I stopped processing.
General difficulty
The Problem is: predicates on some columns, ORDER BY on a different column.
In your fast query, without ORDER BY, the first (arbitrary) 10 rows ...
answered Jan 19 '16 at 16:49
Erwin Brandstetter
116k16 gold badges260 silver badges396 bronze badges
11
You can find out the storage size with
SELECT typname, typlen FROM pg_type WHERE typname IN ('bool', 'int4');
typname | typlen
---------+--------
bool | 1
int4 | 4
(2 rows)
However you need to take alignment into account:
SELECT typname, typlen, typalign FROM pg_type WHERE typname IN ('bool', 'int4');
typname | typlen | typalign
---...
10
Your table is big, and so is any index spanning the whole table. Assuming that:
only new data (with timestamp = now()) is entered
existing rows are neither changed nor deleted.
you have data since 2012-01-01 but queries are predominantly on the current year (?)
I would suggest a partial, multi-column (covering!) index:
CREATE INDEX ON energy_energyentry (...
10
There might be hardware issues, too - how should we know? But there are certainly issues with the query.
First of all, remove DISTINCT from your VIEW definition. It's doing nothing at all (but complicating and slowing things down). Related answer on SO with explanation:
PostgreSQL - Slow query joining on a VIEW
Arriving at this (cleaned up) query:
SELECT ...
10
To be clear, I'd use union as ypercube suggests, but it is also possible with arrays:
select distinct unnest( array_agg(distinct a)||
array_agg(distinct b)||
array_agg(distinct c)||
array_agg(distinct d) )
from t
order by 1;
| unnest |
| :----- |
| 0 |
| 1 |
| 2 |
| 3 ...
answered May 28 '15 at 15:55
Jack says try topanswers.xyz
34.1k13 gold badges88 silver badges163 bronze badges
10
I've done a lot of experimenting and here are my findings.
GIN and sorting
GIN index currently (as of version 9.4) can not assist ordering.
Of the index types currently supported by PostgreSQL, only B-tree can produce sorted output — the other index types return matching rows in an unspecified, implementation-dependent order.
work_mem
Thanks Chris for ...
10
Preliminary notes
You are using odd data types. character(24)? char(n) is an outdated type and almost always the wrong choice. You have indexes on person_id and join on it repeatedly. integer would be much more efficient for multiple reasons. (Or bigint, if you plan to burn more than 2 billion rows over the lifetime of the table.) Related:
Would index ...
Only top voted, non community-wiki answers of a minimum length are eligible
