Query not using index for user defined type

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Apr 15, 2013 at 7:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

I just added implicit cast from my type to int8, numeric and float.
No implicit cast for lower level types.
Isn't it safe?
The problem would be only about the index?