From: | Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: xml data type implications of no = |
Date: | 2010-05-27 01:37:50 |
Message-ID: | 4BFDCCEE.4010103@catalyst.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On 25/05/10 16:43, Mark Kirkwood wrote:
> Today I ran into some interesting consequences of the xml data type
> being without an "=" operator. One I thought I'd post here because it
> has a *possible* planner impact. I'm not sure it is actually a bug as
> such, but this seemed the best forum to post in initially:
>
> test=# \d bug
> Table "public.bug"
> Column | Type | Modifiers
> --------+---------+-----------
> id | integer |
> val | xml |
>
> test=# explain select val::text from bug;
> QUERY PLAN
> --------------------------------------------------------------
> Seq Scan on bug (cost=0.00..58127.78 rows=1000278 width=32)
>
>
> Note the width estimate. However a more realistic estimate for width is:
>
> test=# select 8192/(reltuples/relpages) as width from pg_class where
> relname='bug';
> width
> ------------------
> 394.130431739976
>
> So we are going to massively underestimate the "size" of such a
> dataset. Now this appears to be a consequence of no "=" operator
> (std_typanalyze in analyze.c bails if there isn't one), so the planner
> has no idea about how wide 'val' actually is. I'm wondering if it is
> worth having at least an "=" operator to enable some minimal stats to
> be available for xml columns.
>
Adding a minimal = op (see attached) and an analyze results in:
test=# explain select val::text from bug;
QUERY PLAN
---------------------------------------------------------------
Seq Scan on bug (cost=0.00..62632.08 rows=1000008 width=385)
which gives a much better indication of dataset size.
Attachment | Content-Type | Size |
---|---|---|
xmleq.sql | text/x-sql | 409 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-05-27 02:05:03 | Re: BUG #5468: Pg doesn't send accepted root CA list to client during SSL client cert request |
Previous Message | Robert Haas | 2010-05-26 20:13:00 | Re: BUG #5469: regexp_matches() has poor behaviour and more poor documentation |