Thread: Query not using index for user defined type
Hi guys.
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
Does anybody know why?
On Mon, Apr 15, 2013 at 3:08 PM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote: > > Here is the explain analyze with 1000 entries: > > explain analyze select * from mytable where a > 120::mytype and a < > 530::mytype; > I'm not sure this is appropiate for -hackers, maybe should post on -general. Also provide scripts with the creation of the datatype and/or the OPERATOR FAMILY anyway, this explain is completely useless to prove your point. if you are selecting almost half of the table it will use a seq scan. try again with more rows in the table and a narrow filter -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157
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
On Mon, Apr 15, 2013 at 5:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Using a seqscan to fetch 400 out of 1000 entries is the right thing.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
(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
Rodrigo Barboza <rodrigombufrj@gmail.com> writes:
> 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.
Yeah. One reason why there's not an unsigned int type already is that
it seems impossible to shoehorn it into the numeric promotion hierarchy
without breaking a lot of existing cases. You definitely aren't likely
to get nice results by just adding some implicit casts without doing a
very careful design beforehand.
regards, tom lane
<div dir="ltr"><br /><div class="gmail_extra"><br /><br /><div class="gmail_quote">On Mon, Apr 15, 2013 at 7:51 PM, Tom Lane<span dir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us" target="_blank">tgl@sss.pgh.pa.us</a>></span> wrote:<br /><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">RodrigoBarboza <<a href="mailto:rodrigombufrj@gmail.com">rodrigombufrj@gmail.com</a>> writes:<br /> >I created a implic cast for mytype to bigint.<br /> > So when I do the same query it does seq scan, because the columnis<br /> > transformed into bigint.<br /><br /></div>Yeah. One reason why there's not an unsigned int type alreadyis that<br /> it seems impossible to shoehorn it into the numeric promotion hierarchy<br /> without breaking a lotof existing cases. You definitely aren't likely<br /> to get nice results by just adding some implicit casts withoutdoing a<br /> very careful design beforehand.<br /><br /> regards, tom lane<br /></blockquote></div><br/></div><div class="gmail_extra" style="style">I just added implicit cast from my type to int8, numericand float.</div><div class="gmail_extra" style="style">No implicit cast for lower level types.</div><div class="gmail_extra"style="style"> Isn't it safe?</div><div class="gmail_extra" style="style">The problem would be only aboutthe index?</div></div>