76
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
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 ...
41
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/...
37
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
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 ...
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
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 ...
18
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 ...
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 index ...
16
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 ...
16
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
126k16 gold badges290 silver badges430 bronze badges
15
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 ...
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
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 ...
answered Feb 12 '15 at 21:56
Erwin Brandstetter
126k16 gold badges290 silver badges430 bronze badges
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, '...
13
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 (...
13
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
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 ...
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
126k16 gold badges290 silver badges430 bronze badges
12
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 ...
12
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
---...
11
Using the Database Object Size Functions mentioned above:
SELECT primary_key, pg_column_size(tablename.*) FROM tablename;
11
work_mem
That's what makes your sort expensive:
Sort Method: external merge Disk: 3904kB
The sort spills to disk, which kills performance. You need more RAM. In particular, you need to increase the setting for work_mem. The manual:
work_mem (integer)
Specifies the amount of memory to be used by internal sort operations
and hash tables before ...
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
126k16 gold badges290 silver badges430 bronze badges
11
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 ...
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 ...
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
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
