Thread: BUG #8525: using ltree <@ ltree[] leads too very bad cost estimates
BUG #8525: using ltree <@ ltree[] leads too very bad cost estimates
From
pavel.rosputko@gmail.com
Date:
The following bug has been logged on the website: Bug reference: 8525 Logged by: Pavel Rosputko Email address: pavel.rosputko@gmail.com PostgreSQL version: 9.3.0 Operating system: Linux Description: postgres@a2access=# explain (analyze) select * from locations where text2ltree(coalesce(replace(ancestry, '/', '.'), '')) || id::text <@ '71'; QUERY PLAN ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ Seq Scan on locations (cost=0.00..55.65 rows=27 width=90) (actual time=0.134..3.113 rows=24 loops=1) Filter: ((text2ltree(COALESCE(replace((ancestry)::text, '/'::text, '.'::text), ''::text)) || (id)::tex⦠â¦t) <@ '71'::ltree) Rows Removed by Filter: 1322 note rows=27 and actual rows=24 whereas postgres@a2access=# explain (analyze) select * from locations where text2ltree(coalesce(replace(ancestry, '/', '.'), '')) || id::text <@ array['71']::ltree[]; QUERY PLAN ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ Seq Scan on locations (cost=0.00..55.65 rows=1 width=90) (actual time=0.141..3.279 rows=24 loops=1) Filter: ((text2ltree(COALESCE(replace((ancestry)::text, '/'::text, '.'::text), ''::text)) || (id)::tex⦠â¦t) <@ '{71}'::ltree[]) Rows Removed by Filter: 1322 -> rows=1