BUG #8525: using ltree <@ ltree[] leads too very bad cost estimates

From: pavel(dot)rosputko(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #8525: using ltree <@ ltree[] leads too very bad cost estimates
Date: 2013-10-14 18:40:51
Message-ID: E1VVn4V-0008Qy-EY@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 8525
Logged by: Pavel Rosputko
Email address: pavel(dot)rosputko(at)gmail(dot)com
PostgreSQL version: 9.3.0
Operating system: Linux
Description:

postgres(at)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(at)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

Browse pgsql-bugs by date

  From Date Subject
Next Message Josh Kupershmidt 2013-10-14 21:38:22 Re: Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist
Previous Message Marko Tiikkaja 2013-10-14 13:23:51 Re: BUG #8461: PostgreSQL 9.3 pg_dump heap corruptions