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 ...
45
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) ...
44
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/...
38
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 ...
25
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
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
132k17 gold badges310 silver badges454 bronze badges
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
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
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 affects performance. Effective storage requirements depend on whether the null bitmap for each row already exists and has room for 3 more bits.
Tables with up to ...
answered Feb 12 '15 at 21:56
Erwin Brandstetter
132k17 gold badges310 silver badges454 bronze badges
16
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
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 ...
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 ...
13
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
Using the Database Object Size Functions mentioned above:
SELECT primary_key, pg_column_size(tablename.*) FROM tablename;
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
132k17 gold badges310 silver badges454 bronze badges
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
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
132k17 gold badges310 silver badges454 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 ...
11
Interesting question - which has many possibilities/permutations (this answer has been extensively revised).
Basically, this problem can be divided into two main streams.
A single random record
Multiple random records (not in the question - see reference and discussion at bottom)
Having researched this, I believe that the fastest solution to the single ...
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
Query
This query should be substantially faster in any case:
SELECT parent_id, message_id, posted_at, share_count
FROM messages
WHERE feed_id = 7
AND posted_at >= '2015-01-01 4:0:0'
AND posted_at < '2015-04-28 4:0:0'
AND parent_id IS NULL -- match index condition
UNION ALL
(
SELECT DISTINCT ON(parent_id)
parent_id, message_id, ...
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
