pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Attempt at work around of int4 query won't touch int8 index ...
5+ messages / 4 participants
[nested] [flat]

* Attempt at work around of int4 query won't touch int8 index ...
@ 2003-09-11 01:13 James Robinson <jlrobins@socialserve.com>
  2003-09-11 02:44 ` Re: Attempt at work around of int4 query won't touch int8 index ... Tom Lane <tgl@sss.pgh.pa.us>
  2003-09-15 07:42 ` Re: Attempt at work around of int4 query won't touch int8 index ... Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  0 siblings, 2 replies; 5+ messages in thread

From: James Robinson @ 2003-09-11 01:13 UTC (permalink / raw)
  To: 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




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

* Re: Attempt at work around of int4 query won't touch int8 index ...
  2003-09-11 01:13 Attempt at work around of int4 query won't touch int8 index ... James Robinson <jlrobins@socialserve.com>
@ 2003-09-11 02:44 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2003-09-11 02:44 UTC (permalink / raw)
  To: James Robinson <jlrobins@socialserve.com>; +Cc: pgsql-performance

James Robinson <jlrobins@socialserve.com> writes:
> 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?

I think it's a dead end.  What I was playing with this afternoon was
removing the int8-and-int4 comparison operators from pg_operator.
It works as far as making "int8col = 42" do the right thing, but I'm
not sure yet about side-effects.

			regards, tom lane



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

* Re: Attempt at work around of int4 query won't touch int8 index ...
  2003-09-11 01:13 Attempt at work around of int4 query won't touch int8 index ... James Robinson <jlrobins@socialserve.com>
@ 2003-09-15 07:42 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-09-16 07:44   ` Re: Attempt at work around of int4 query won't touch int8 index ... Paul Thomas <paul@tmsl.demon.co.uk>
  1 sibling, 1 reply; 5+ messages in thread

From: Shridhar Daithankar @ 2003-09-15 07:42 UTC (permalink / raw)
  To: pgsql-performance

On 10 Sep 2003 at 22:44, Tom Lane wrote:

> James Robinson <jlrobins@socialserve.com> writes:
> > 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?
> 
> I think it's a dead end.  What I was playing with this afternoon was
> removing the int8-and-int4 comparison operators from pg_operator.
> It works as far as making "int8col = 42" do the right thing, but I'm
> not sure yet about side-effects.

Is it possible to follow data type upgrade model in planner?  Something like in 
C/C++ where data types are promoted upwards to find out better plan?

int2->int4->int8->float4->float8 types.

 That could be a clean solution..

just a thought..

Bye
 Shridhar

--
Hlade's Law:	If you have a difficult task, give it to a lazy person --	they 
will find an easier way to do it.




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

* Re: Attempt at work around of int4 query won't touch int8 index ...
  2003-09-11 01:13 Attempt at work around of int4 query won't touch int8 index ... James Robinson <jlrobins@socialserve.com>
  2003-09-15 07:42 ` Re: Attempt at work around of int4 query won't touch int8 index ... Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-09-16 07:44   ` Paul Thomas <paul@tmsl.demon.co.uk>
  2003-09-16 14:05     ` Re: Attempt at work around of int4 query won't touch int8 index ... Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Paul Thomas @ 2003-09-16 07:44 UTC (permalink / raw)
  To: pgsql-performance


On 15/09/2003 08:42 Shridhar Daithankar wrote:
> 
> Is it possible to follow data type upgrade model in planner?  Something
> like in
> C/C++ where data types are promoted upwards to find out better plan?
> 
> int2->int4->int8->float4->float8 types.
> 
>  That could be a clean solution..
> 
> just a thought..
> 

Interestingly, float8 indexes do work OK (float8col = 99). I spend a large 
part of yesterday grepping through the sources to try and find out why 
this should be so. No luck so far but I'm going to keep on trying!


-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller 
Business |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+



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

* Re: Attempt at work around of int4 query won't touch int8 index ...
  2003-09-11 01:13 Attempt at work around of int4 query won't touch int8 index ... James Robinson <jlrobins@socialserve.com>
  2003-09-15 07:42 ` Re: Attempt at work around of int4 query won't touch int8 index ... Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-09-16 07:44   ` Re: Attempt at work around of int4 query won't touch int8 index ... Paul Thomas <paul@tmsl.demon.co.uk>
@ 2003-09-16 14:05     ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2003-09-16 14:05 UTC (permalink / raw)
  To: Paul Thomas <paul@tmsl.demon.co.uk>; +Cc: pgsql-performance

Paul Thomas <paul@tmsl.demon.co.uk> writes:
> On 15/09/2003 08:42 Shridhar Daithankar wrote:
>> Is it possible to follow data type upgrade model in planner?

We have one, more or less.  It's not explicitly coded, it emerges from
the fact that certain casts are implicit and others are not.  For
instance, int4->float8 is implicit but float8->int4 is not.

> Interestingly, float8 indexes do work OK (float8col = 99). I spend a large 
> part of yesterday grepping through the sources to try and find out why 
> this should be so. No luck so far but I'm going to keep on trying!

The reason that case works is that there is no float8 = int4 operator.
The parser can find no other interpretation than promoting the int4 to
float8 and using float8 = float8.  (The dual possibility, coerce float8
to int4 and use int4 = int4, is not considered because that coercion
direction is not implicit.)  So you end up with an operator that matches
the float8 index, and all is well.

The int8 case fails because there is a cross-type operator int8 = int4,
and the parser prefers that since it's an exact match to the initial
data types.  But it doesn't match the int8 index.

We've floated various proposals for solving this, such as getting rid of
cross-type operators, but none so far have passed the test of not having
bad side-effects.  See the pg_hackers archives for details (and *please*
don't waste this list's bandwidth with speculating about solutions until
you've absorbed some of the history.  This topic has been heard of
before ;-).)

			regards, tom lane



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


end of thread, other threads:[~2003-09-16 14:05 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-11 01:13 Attempt at work around of int4 query won't touch int8 index ... James Robinson <jlrobins@socialserve.com>
2003-09-11 02:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-15 07:42 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-09-16 07:44   ` Paul Thomas <paul@tmsl.demon.co.uk>
2003-09-16 14:05     ` Tom Lane <tgl@sss.pgh.pa.us>

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