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;