101
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 ...
67
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 ...
42
It very much depends on circumstances and exact requirements. Consider my comment to the question.
Simple solution
With DISTINCT ON in Postgres:
SELECT DISTINCT ON (i.good, i.the_date)
i.the_date, p.the_date AS pricing_date, i.good, p.price
FROM inventory i
LEFT JOIN price p ON i.good = p.good AND i.the_date >= p.the_date
ORDER BY i.good, ...
35
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 ...
34
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 ...
31
Short answer: integer is faster than varchar or text in every aspect. Won't matter much for small tables and / or short keys. The difference grows with the length of the keys and the number of rows.
string ... 20 characters long, which in memory is roughly 5x that of
the integer (if an integer is 4 bytes, and the strings are pure ASCII
at 1 byte per ...
30
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 ...
28
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/...
27
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 ...
18
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
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 ...
16
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
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 ...
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 ...
13
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 ...
13
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
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 ...
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 ...
11
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
105k11 gold badges225 silver badges354 bronze badges
11
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
105k11 gold badges225 silver badges354 bronze badges
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
105k11 gold badges225 silver badges354 bronze badges
11
Isn't the implicit syntax just rewritten to be the same as the explicit syntax?
Not necessarily. You are building on slightly incorrect assumptions. Like I explained under the referenced question:
What does [FROM x, y] mean in Postgres?
Comma-separated items in the FROM list are almost, but not quite identical to explicit CROSS JOIN notation. Explicit ...
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 ...
10
"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
105k11 gold badges225 silver badges354 bronze badges
10
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 ...
10
Yes, there can be downsides. If another query looks at a different data segment not determined by the date, it might take a performance hit if rows are spread out over more data pages now. Just the same way as your first query profits. That completely depends on information not in your question.
other queries using a PK of table (let say id_foo)
That ...
9
The biggest difference in time in your execution plans is on the top node, the UPDATE itself. This suggests that most of your time is going to IO during the update. You could verify this by turning on track_io_timing and running the queries with EXPLAIN (ANALYZE, BUFFERS)
The different plans are presenting rows to be updated in different orders. One is ...
9
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 (...
Only top voted, non community-wiki answers of a minimum length are eligible
