pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Using bigint needs explicit cast to use the index
6+ messages / 4 participants
[nested] [flat]

* Using bigint needs explicit cast to use the index
@ 2004-03-08 00:26  Steven Butler <stevenb@kjross.com.au>
  0 siblings, 2 replies; 6+ messages in thread

From: Steven Butler @ 2004-03-08 00:26 UTC (permalink / raw)
  To: pgsql-performance

Hi,

I've recently converted a database to use bigint for the indices.  Suddenly
simple queries like

select * from new_test_result where parent_id = 2

are doing full table scans instead of using the index.  The table has over 4
million rows, of which only 30 or so would be selected by the query.

The database table in question was fully vacuumed and clustered on the
index.

I tried disabling seqscan, but it still did full table scan.  After browsing
around a bit, I had a hunch it might be failing to use the index because it
is perhaps converting the parent_id to an integer, and I don't have a
functional index on that (wouldn't seem correct either).

I tested my hunch by casting the constant to bigint (as can be seen below)
and suddenly the query is using the index again.

We are currently using pg 7.3.4.  Is this intended behaviour?  Should the
constant be cast to the type of the table column where possible, or should
it be the other way around?  If this is considered a bug, is it already
fixed, in 7.3.6 or 7.4.x?

Kind Regards,
Steve Butler



steve=# \d new_test_result;
                            Table "public.new_test_result"
  Column   |  Type   |                            Modifiers
-----------+---------+------------------------------------------------------
-----------
 id        | bigint  | not null default
nextval('public.new_test_result_id_seq'::text)
 parent_id | bigint  |
 testcode  | text    |
 testtype  | text    |
 testdesc  | text    |
 pass      | integer |
 latency   | integer |
 bytessent | integer |
 bytesrecv | integer |
 defect    | text    |
Indexes: test_result_parent_id_fk btree (parent_id)
Foreign Key constraints: $1 FOREIGN KEY (parent_id) REFERENCES
new_test_run(id) ON UPDATE NO ACTION ON DELETE CASCADE

steve=# explain select * from new_test_result where parent_id = 2;
                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on new_test_result  (cost=0.00..123370.57 rows=23 width=125)
   Filter: (parent_id = 2)
(2 rows)

steve=# explain select * from new_test_result where parent_id = 2::bigint;
                                            QUERY PLAN
----------------------------------------------------------------------------
-----------------------
 Index Scan using test_result_parent_id_fk on new_test_result
(cost=0.00..3.32 rows=23 width=125)
   Index Cond: (parent_id = 2::bigint)
(2 rows)




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Using bigint needs explicit cast to use the index
@ 2004-03-08 00:43  Neil Conway <neilc@samurai.com>
  parent: Steven Butler <stevenb@kjross.com.au>
  1 sibling, 0 replies; 6+ messages in thread

From: Neil Conway @ 2004-03-08 00:43 UTC (permalink / raw)
  To: Steven Butler <stevenb@kjross.com.au>; +Cc: pgsql-performance

Steven Butler wrote:
> I've recently converted a database to use bigint for the indices.  Suddenly
> simple queries like
> 
> select * from new_test_result where parent_id = 2
> 
> are doing full table scans instead of using the index.

This is fixed in CVS HEAD. In the mean time, you can enclose the 
integer literal in single quotes, or explicitely cast it to the type 
of the column.

FWIW, this is an FAQ.

-Neil



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Using bigint needs explicit cast to use the index
@ 2004-03-08 16:05  Andrew Sullivan <ajs@crankycanuck.ca>
  parent: Steven Butler <stevenb@kjross.com.au>
  1 sibling, 2 replies; 6+ messages in thread

From: Andrew Sullivan @ 2004-03-08 16:05 UTC (permalink / raw)
  To: pgsql-performance

On Mon, Mar 08, 2004 at 10:26:21AM +1000, Steven Butler wrote:
> I tested my hunch by casting the constant to bigint (as can be seen below)
> and suddenly the query is using the index again.

Yes.  You can make this work all the time by quoting the constant. 
That is, instead of

	WHERE indexcolumn = 123

do

	WHERE indexcolumn = '123'

 
> We are currently using pg 7.3.4.  Is this intended behaviour?  Should the
> constant be cast to the type of the table column where possible, or should

"Intended", no.  "Expected", yes.  This topic has had the best
Postgres minds work on it, and so far nobody's come up with a
solution.  There was a proposal to put in a special-case automatic
fix for int4/int8 in 7.4, but I don't know whether it made it in.

A
-- 
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what 
you told them to.  That actually seems sort of quaint now.
		--J.D. Baldwin



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Using bigint needs explicit cast to use the index
@ 2004-03-08 16:22  Neil Conway <neilc@samurai.com>
  parent: Andrew Sullivan <ajs@crankycanuck.ca>
  1 sibling, 1 reply; 6+ messages in thread

From: Neil Conway @ 2004-03-08 16:22 UTC (permalink / raw)
  To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-performance

Andrew Sullivan wrote:
> "Intended", no.  "Expected", yes.  This topic has had the best
> Postgres minds work on it, and so far nobody's come up with a
> solution.

Actually, this has already been fixed in CVS HEAD (as I mentioned in 
this thread yesterday). To wit:

nconway=# create table t1 (a int8);
CREATE TABLE
nconway=# create index t1_a_idx on t1 (a);
CREATE INDEX
nconway=# explain select * from t1 where a = 5;
                              QUERY PLAN
--------------------------------------------------------------------
  Index Scan using t1_a_idx on t1  (cost=0.00..17.07 rows=5 width=8)
    Index Cond: (a = 5)
(2 rows)
nconway=# select version();
                                       version
------------------------------------------------------------------------------------
  PostgreSQL 7.5devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 
3.3.3 (Debian)
(1 row)

-Neil



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Using bigint needs explicit cast to use the index
@ 2004-03-08 16:33  Bruno Wolff III <bruno@wolff.to>
  parent: Andrew Sullivan <ajs@crankycanuck.ca>
  1 sibling, 0 replies; 6+ messages in thread

From: Bruno Wolff III @ 2004-03-08 16:33 UTC (permalink / raw)
  To: pgsql-performance

On Mon, Mar 08, 2004 at 11:05:25 -0500,
  Andrew Sullivan <ajs@crankycanuck.ca> wrote:
> 
> "Intended", no.  "Expected", yes.  This topic has had the best
> Postgres minds work on it, and so far nobody's come up with a
> solution.  There was a proposal to put in a special-case automatic
> fix for int4/int8 in 7.4, but I don't know whether it made it in.

This is handled better in 7.5. Instead of doing things deciding what
types of type conversion to do, a check is make for cross type conversion
functions that could be used for an index scan. This is a general solution
that doesn't result in unexpected type conversions.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Using bigint needs explicit cast to use the index
@ 2004-03-08 16:46  Andrew Sullivan <ajs@crankycanuck.ca>
  parent: Neil Conway <neilc@samurai.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Andrew Sullivan @ 2004-03-08 16:46 UTC (permalink / raw)
  To: pgsql-performance

On Mon, Mar 08, 2004 at 11:22:56AM -0500, Neil Conway wrote:
> Actually, this has already been fixed in CVS HEAD (as I mentioned in 
> this thread yesterday). To wit:

Yes, I saw that after I sent my mail.  What can I say except, "Yay! 
Good work!"

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
		--Dennis Ritchie



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2004-03-08 16:46 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-08 00:26 Using bigint needs explicit cast to use the index Steven Butler <stevenb@kjross.com.au>
2004-03-08 00:43 ` Neil Conway <neilc@samurai.com>
2004-03-08 16:05 ` Andrew Sullivan <ajs@crankycanuck.ca>
2004-03-08 16:22   ` Neil Conway <neilc@samurai.com>
2004-03-08 16:46     ` Andrew Sullivan <ajs@crankycanuck.ca>
2004-03-08 16:33   ` Bruno Wolff III <bruno@wolff.to>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox