BUG #8525: using ltree <@ ltree[] leads too very bad cost estimates - Mailing list pgsql-bugs

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