Re: Terribly slow query with very good plan?

[Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx>]

On Fri, 4 Feb 2022 at 14:41, Les <nagylzs@xxxxxxxxx> wrote:

Hello,

The "replath" field contains the path of the file/folder. For example: "/folder1/folder2/folder3/filename4.ext5".  The replath field is managed by triggers. There are about 1M rows for files and 600K folder rows in the database. The files are well distributed between folders, and there are only 45 root folders ( parent_id is null)

Replying in a separate thread, just in case this does not help.

It seems you already store relpath but as text via triggers, will the 'ltree' extension be of any help to get your results faster (to help implement path enumeration, but has a limitation of 65K objects)

https://www.postgresql.org/docs/current/ltree.html

https://github.com/postgres/postgres/blob/master/contrib/ltree/sql/ltree.sql  (test cases for elaborate examples)

https://patshaughnessy.net/2017/12/14/manipulating-trees-using-sql-and-the-postgres-ltree-extension

also, another pattern i came across was via closure tables

https://www.slideshare.net/billkarwin/models-for-hierarchical-data

https://stackoverflow.com/questions/19834400/what-is-the-simplest-way-to-save-a-file-tree-in-a-postgres-database/19835575

ex. (from the doc)

postgres=# drop table test;

DROP TABLE

postgres=# CREATE TABLE test (path ltree);

INSERT INTO test VALUES ('Top');

INSERT INTO test VALUES ('Top.Science');

INSERT INTO test VALUES ('Top.Science.Astronomy');

INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');

INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');

INSERT INTO test VALUES ('Top.Hobbies');

INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');

INSERT INTO test VALUES ('Top.Collections');

INSERT INTO test VALUES ('Top.Collections.Pictures');

INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');

INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');

INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');

INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');

CREATE INDEX path_gist_idx ON test USING GIST (path);

CREATE INDEX path_idx ON test USING BTREE (path); -- we can even make this unique index as there would only be one path

-- we can also create partial indexes depending on the query pattern 

 

#my focus is no rows filtered (hence less wasted operations)

postgres=# analyze test;

ANALYZE

postgres=# explain analyze select exists (SELECT 1 FROM test WHERE path ~ '*.Stars');

                                              QUERY PLAN

------------------------------------------------------------------------------------------------------

 Result  (cost=1.16..1.17 rows=1 width=1) (actual time=0.010..0.010 rows=1 loops=1)

   InitPlan 1 (returns $0)

     ->  Seq Scan on test  (cost=0.00..1.16 rows=1 width=0) (actual time=0.008..0.009 rows=1 loops=1)

           Filter: (path ~ '*.Stars'::lquery)

           Rows Removed by Filter: 10

 Planning Time: 0.248 ms

 Execution Time: 0.023 ms

(7 rows)

postgres=# set enable_seqscan TO 0; -- small table, hence

SET

postgres=# explain analyze select exists (SELECT 1 FROM test WHERE path ~ '*.Stars');

                                                         QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------

 Result  (cost=8.15..8.16 rows=1 width=1) (actual time=0.020..0.021 rows=1 loops=1)

   InitPlan 1 (returns $0)

     ->  Index Scan using path_gist_idx on test  (cost=0.13..8.15 rows=1 width=0) (actual time=0.019..0.019 rows=1 loops=1)

           Index Cond: (path ~ '*.Stars'::lquery)

 Planning Time: 0.079 ms

 Execution Time: 0.037 ms

(6 rows)

Please ignore, if not relevant to the discussion.

--

Thanks,

Vijay

LinkedIn - Vijaykumar Jain