From: | Rodrigo Barboza <rodrigombufrj(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Query not using index for user defined type |
Date: | 2013-04-15 20:44:42 |
Message-ID: | CANs8QJZifmG13qTB9ohDDb=KfZRhBee5NK4vo=1ihBpneogVuA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Apr 15, 2013 at 5:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Rodrigo Barboza <rodrigombufrj(at)gmail(dot)com> writes:
> > I created a type 'mytype' (an unsigned int) and created an operator class
> > for index.
> > Then I created a table with a column of my type and isnerted 1000
> entries.
> > But no matter how many entries I have in the table, it never uses the
> > index. It always does a seq scan.
>
> > Here is the explain analyze with 1000 entries:
>
> > explain analyze select * from mytable where a > 120::mytype and a <
> > 530::mytype;
>
> >
> ---------------------------------------------------------------------------------------------------
> > Seq Scan on mytable (cost=0.00..19.02 rows=400 width=4) (actual
> > time=0.023..0.229 rows=409 loops=1)
> > Filter: ((a > '120'::mytype) AND (a < '530'::mytpe))
> > Total runtime: 0.297 ms
>
> Using a seqscan to fetch 400 out of 1000 entries is the right thing.
> (The crossover point where an index becomes unhelpful is a lot closer
> to 1% of the table than it is to 40%.)
>
> regards, tom lane
>
You were right Tom, when I did < 200 it used the index.
But I have another question.
I created a implic cast for mytype to bigint.
So when I do the same query it does seq scan, because the column is
transformed into bigint.
Is there a good solution for this?
Here is an examples with the 2 queries.
explain analyze select * from mytable where a < 200::mytype;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Index Scan using mytype_idx on tm32 (cost=0.00..11.66 rows=195 width=4)
(actual time=0.020..0.068 rows=200 loops=1)
Index Cond: (a < '200'::mytype)
Total runtime: 0.111 ms
explain analyze select * from mytable where a < 200;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on mytable (cost=0.00..19.02 rows=334 width=4) (actual
time=0.015..0.215 rows=200 loops=1)
Filter: ((a)::bigint < 200)
Total runtime: 0.238 ms
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2013-04-15 20:53:51 | Re: COPY and Volatile default expressions |
Previous Message | Andrew Dunstan | 2013-04-15 20:41:53 | Re: Why are JSON extraction functions STABLE and not IMMUTABLE? |