I run it from inside the machine on the local database.
For example :
db=# create table rule_test as select generate_series(1,100000000);
SELECT 100000000
db=# explain analyze select generate_series from rule_test order by generate_series asc;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Sort (cost=17763711.32..18045791.04 rows=112831890 width=4) (actual time=62677.752..100928.829 rows=100000000 loops=1)
Sort Key: generate_series
Sort Method: external merge Disk: 1367624kB
-> Seq Scan on rule_test (cost=0.00..1570796.90 rows=112831890 width=4) (actual time=0.019..36098.463 rows=100000000 loops=1)
Planning time: 0.072 ms
Execution time: 107025.113 ms
(6 rows)
db=# create index on rule_test(generate_series);
CREATE INDEX
db=# select generate_series from rule_test order by generate_series asc;
db=# explain analyze select generate_series from rule_test order by generate_series asc;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using rule_test_generate_series_idx on rule_test (cost=0.57..2490867.57 rows=100000000 width=4) (actual time=0.103..63122.906 rows=100000000 loops=1)
Heap Fetches: 100000000
Planning time: 6.682 ms
Execution time: 69265.311 ms
(4 rows)
db=# select generate_series from rule_test order by generate_series asc;
stuck for more then a hour
בתאריך יום א׳, 25 בנוב׳ 2018 ב-15:30 מאת Justin Pryzby <pryzby@xxxxxxxxxxxxx>:
Cc: pgsql-performance@xxxxxxxxxxxxxxxxxxxx,
pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Please avoid simultaneously sending the same question to multiple lists.
It means that people can't see each others replies and everything that implies.
On Sun, Nov 25, 2018 at 03:08:33PM +0200, Mariel Cherkassky wrote:
> However when I run the query without the explain analyze it takes forever
> to run it(More then two hours).
> Is there a possibility that it is related to fetching or something like
> that ?
If it's a remote database, I expect that's why.
Maybe you can test by running the query on the DB server.
Or by running another variant of the query, such as:
WITH x AS (QUERY GOES HERE) SELECT 1;
which returns only one row but after having executed the query behind CTE, as
optimization fence.
Justin
