Re: Query not using index for user defined type - Mailing list pgsql-hackers

From Rodrigo Barboza
Subject Re: Query not using index for user defined type
Date
Msg-id CANs8QJZifmG13qTB9ohDDb=KfZRhBee5NK4vo=1ihBpneogVuA@mail.gmail.com
Whole thread Raw
In response to Re: Query not using index for user defined type  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Query not using index for user defined type
List pgsql-hackers



On Mon, Apr 15, 2013 at 5:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Rodrigo Barboza <rodrigombufrj@gmail.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

pgsql-hackers by date:

Previous
From: Andrew Dunstan
Date:
Subject: Re: Why are JSON extraction functions STABLE and not IMMUTABLE?
Next
From: Simon Riggs
Date:
Subject: Re: COPY and Volatile default expressions