pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Steven Butler <stevenb@kjross.com.au>
To: pgsql-performance@postgresql.org
Subject: Using bigint needs explicit cast to use the index
Date: Mon, 8 Mar 2004 10:26:21 +1000
Message-ID: <001101c404a3$fb34d320$fd08a8c0@steve> (raw)

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)




view thread (6+ messages)  latest in thread

Message-ID: <001101c404a3$fb34d320$fd08a8c0@steve>
Permalink:  ../001101c404a3$fb34d320$fd08a8c0@steve/
Also on:    postgresql.org/message-id/001101c404a3$fb34d320$fd08a8c0@steve

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: stevenb@kjross.com.au
  Subject: Re: Using bigint needs explicit cast to use the index
  In-Reply-To: <001101c404a3$fb34d320$fd08a8c0@steve>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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