Skip site navigation (1) Skip section navigation (2)

Attempt at work around of int4 query won't touch int8 index ...

From: James Robinson <jlrobins(at)socialserve(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Attempt at work around of int4 query won't touch int8 index ...
Date: 2003-09-11 01:13:29
Message-ID: 2756862E-E3F5-11D7-8513-000A9566A412@socialserve.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hello,

	I'm trying a work-around on the "index on int8 column gets ignored by  
planner when queried by literal numbers lacking the explicit '::int8'"  
issue, and had hoped that perhaps I could create a functional index on  
the result of casting the pk field to int4, and mabye with a little  
luck the planner would consider the functional index instead. Here's  
what I'm playing with on 7.3.4:

social=# create table foo (id int8 primary key, stuff text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
'foo_pkey' for table 'foo'
CREATE TABLE
social=# create index foo_pkey_int4 on foo(int4(id));
CREATE INDEX

social=# explain analyze select id from foo where id = 42;
                                          QUERY PLAN
------------------------------------------------------------------------ 
--------------------
  Seq Scan on foo  (cost=0.00..22.50 rows=1 width=8) (actual  
time=0.01..0.01 rows=0 loops=1)
    Filter: (id = 42)
  Total runtime: 0.15 msec
(3 rows)

social=# explain analyze select id from foo where id = 42::int8;
                                                  QUERY PLAN
------------------------------------------------------------------------ 
------------------------------------
  Index Scan using foo_pkey on foo  (cost=0.00..4.82 rows=1 width=8)  
(actual time=0.02..0.02 rows=0 loops=1)
    Index Cond: (id = 42::bigint)
  Total runtime: 0.09 msec
(3 rows)

social=# explain analyze select id from foo where id = int4(33);
                                          QUERY PLAN
------------------------------------------------------------------------ 
--------------------
  Seq Scan on foo  (cost=0.00..22.50 rows=1 width=8) (actual  
time=0.01..0.01 rows=0 loops=1)
    Filter: (id = 33)
  Total runtime: 0.07 msec
(3 rows)

Is this just a dead end, or is there some variation of this that might  
possibly work, so that ultimately an undoctored literal number, when  
applied to an int8 column, could find an index?

Thanks,
James


Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2003-09-11 02:44:17
Subject: Re: Attempt at work around of int4 query won't touch int8 index ...
Previous:From: Mary Edie MeredithDate: 2003-09-11 00:17:15
Subject: Re: [osdldbt-general] Re: [GENERAL] how to get accurate

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group