All Questions
10
questions
2
votes
2
answers
147
views
Identical SELECT vs DELETE query creates different query plans with vastly different execution time
I am trying to speed up a delete query that appears to be very slow when compared to an identical select query:
Slow delete query:
https://explain.depesz.com/s/kkWJ
delete from processed.token_utxo
...
- 270
1
vote
1
answer
299
views
Why would LIMIT 2 queries work but LIMIT 1 always times out?
I'm using this public Postgres DB of NEAR protocol: https://github.com/near/near-indexer-for-explorer#shared-public-access
postgres://public_readonly:[email protected]/...
- 23.3k
3
votes
2
answers
2k
views
Performance impact of view on aggregate function vs result set limiting
The problem
Using PostgreSQL 13, I ran into a performance issue selecting the highest id from a view that joins two tables, depending on the select statement I execute.
Here's a sample setup:
CREATE ...
- 1,702
8
votes
1
answer
2k
views
How to force evaluation of subquery before joining / pushing down to foreign server
Suppose I want to query a big table with a few WHERE filters. I am using Postgres 11 and a foreign table; foreign data wrapper (FDW) is clickhouse_fdw. But I am also interested in a general solution.
...
- 838
20
votes
3
answers
35k
views
How do I know if any index is used in a query | PostgreSQL 11?
I am little bit confused and need some advice. I use PostgreSQL 11 database. I have such pretty simple sql statement:
SELECT DISTINCT "CITY", "AREA", "REGION"
FROM youtube
WHERE
"CITY" IS NOT ...
- 5,074
1
vote
1
answer
195
views
Adding ORDER BY to SQL query explodes runtime
My query execution time explodes when I add an ORDER BY, I can't seem to get the reasoning behind it. I feel it should be simple to order on an indexed field.
So two queries look like this:
This one ...
- 1,712
1
vote
1
answer
241
views
PostgreSQL query is not using an index
Enviroment
My PostgreSQL (9.2) schema looks like this:
CREATE TABLE first
(
id_first bigint NOT NULL,
first_date timestamp without time zone NOT NULL,
CONSTRAINT first_pkey PRIMARY KEY (...
- 1,740
0
votes
3
answers
57
views
How make this filter sargeable or improve index
vzla_seg are the road network segments. Each segment have his general azimuth. COUNT(*) = 3.849.834
CREATE TABLE vzla_seg (`azimuth` int);
INSERT INTO vzla_seg (`azimuth`)
VALUES
(330), (...
- 47.9k
4
votes
4
answers
3k
views
Postgresql ignoring index on timestamp column even if query is faster using index
On postgresql 9.3, I have a table with a little over a million records, the table was created as:
CREATE TABLE entradas
(
id serial NOT NULL,
uname text,
contenido text,
fecha date,
hora time ...
- 363
4
votes
1
answer
4k
views
Efficient PostgreSQL query on timestamp using index or bitmap index scan?
In PostgreSQL, I have an index on a date field on my tickets table.
When I compare the field against now(), the query is pretty efficient:
# explain analyze select count(1) as count from tickets ...
- 1,040