pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Followup - expression (functional) index use in joins
7+ messages / 4 participants
[nested] [flat]

* Followup - expression (functional) index use in joins
@ 2003-11-26 18:39 Roger Ging <rging@paccomsys.com>
  2003-11-26 19:12 ` Re: Followup - expression (functional) index use in joins Richard Huxton <dev@archonet.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Roger Ging @ 2003-11-26 18:39 UTC (permalink / raw)
  To: pgsql-performance


version 7.4 results:

explain analyse SELECT L.row_id FROM music.logfile L LEFT JOIN 
music.program P ON
music.fn_mri_id_no_program(P.mri_id_no) = L.program_id
WHERE  L.station = UPPER('kabc')::VARCHAR
AND L.air_date = '04/12/2002'::TIMESTAMP
AND P.cutoff_date IS NULL
ORDER BY L.chron_start,L.chron_end;
 
     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=17595.99..17608.23 rows=4894 width=12) (actual 
time=8083.719..8083.738 rows=30 loops=1)
    Sort Key: l.chron_start, l.chron_end
    ->  Merge Left Join  (cost=17135.92..17296.07 rows=4894 width=12) 
(actual time=7727.590..8083.349 rows=30 loops=1)
          Merge Cond: ("outer"."?column5?" = "inner"."?column3?")
          Filter: ("inner".cutoff_date IS NULL)
          ->  Sort  (cost=1681.69..1682.73 rows=414 width=21) (actual 
time=1.414..1.437 rows=30 loops=1)
                Sort Key: (l.program_id)::text
                ->  Index Scan using idx_logfile_station_air_date on 
logfile l  (cost=0.00..1663.70 rows=414 width=21) (actual 
time=0.509..1.228 rows=30 loops=1)
                      Index Cond: (((station)::text = 'KABC'::text) AND 
(air_date = '2002-04-12 00:00:00'::timestamp without time zone))
          ->  Sort  (cost=15454.22..15465.06 rows=4335 width=20) (actual 
time=7718.612..7869.874 rows=152779 loops=1)
                Sort Key: (music.fn_mri_id_no_program(p.mri_id_no))::text
                ->  Seq Scan on program p  (cost=0.00..15192.35 
rows=4335 width=20) (actual time=109.045..1955.882 rows=173998 loops=1)
  Total runtime: 8194.290 ms
(13 rows)


version 7.3 results:

explain analyse SELECT L.row_id FROM music.logfile L LEFT JOIN 
music.program P ON
music.fn_mri_id_no_program(P.mri_id_no) = L.program_id
WHERE  L.station = UPPER('kabc')::VARCHAR
AND L.air_date = '04/12/2002'::TIMESTAMP
AND P.cutoff_date IS NULL
ORDER BY L.chron_start,L.chron_end;
 
  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=55765.51..55768.33 rows=1127 width=41) (actual 
time=7.74..7.75 rows=30 loops=1)
    Sort Key: l.chron_start, l.chron_end
    ->  Nested Loop  (cost=0.00..55708.36 rows=1127 width=41) (actual 
time=0.21..7.62 rows=30 loops=1)
          Filter: ("inner".cutoff_date IS NULL)
          ->  Index Scan using idx_logfile_station_air_date on logfile l 
  (cost=0.00..71.34 rows=17 width=21) (actual time=0.14..0.74 rows=30 
loops=1)
                Index Cond: ((station = 'KABC'::character varying) AND 
(air_date = '2002-04-12 00:00:00'::timestamp without time zone))
          ->  Index Scan using idx_program_mri_id_no_program on program 
p  (cost=0.00..3209.16 rows=870 width=20) (actual time=0.05..0.22 rows=9 
loops=30)
                Index Cond: (music.fn_mri_id_no_program(p.mri_id_no) = 
"outer".program_id)
  Total runtime: 7.86 msec




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

* Re: Followup - expression (functional) index use in joins
  2003-11-26 18:39 Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
@ 2003-11-26 19:12 ` Richard Huxton <dev@archonet.com>
  2003-11-26 21:29   ` Re: Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Richard Huxton @ 2003-11-26 19:12 UTC (permalink / raw)
  To: Roger Ging <rging@paccomsys.com>; pgsql-performance

On Wednesday 26 November 2003 18:39, Roger Ging wrote:
> version 7.4 results:
>
> explain analyse SELECT L.row_id FROM music.logfile L LEFT JOIN
> music.program P ON
> music.fn_mri_id_no_program(P.mri_id_no) = L.program_id
> WHERE  L.station = UPPER('kabc')::VARCHAR
> AND L.air_date = '04/12/2002'::TIMESTAMP
> AND P.cutoff_date IS NULL
> ORDER BY L.chron_start,L.chron_end;

>                 ->  Seq Scan on program p  (cost=0.00..15192.35
> rows=4335 width=20) (actual time=109.045..1955.882 rows=173998 loops=1)

The estimated number of rows here (4335) is *way* off (173998 actually). If 
you only had 4335 rows, then this might be a more sensible plan.

First step is to run:
  VACUUM ANALYSE program;
Then, check the definition of your function fn_mri_id_no_program() and make 
sure it is marked immutable/stable (depending on what it does) and that it's 
returning a varchar.


-- 
  Richard Huxton
  Archonet Ltd



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

* Re: Followup - expression (functional) index use in joins
  2003-11-26 18:39 Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
  2003-11-26 19:12 ` Re: Followup - expression (functional) index use in joins Richard Huxton <dev@archonet.com>
@ 2003-11-26 21:29   ` Roger Ging <rging@paccomsys.com>
  2003-11-27 00:09     ` Re: Followup - expression (functional) index use in joins Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Roger Ging @ 2003-11-26 21:29 UTC (permalink / raw)
  To: pgsql-performance

Ran vacuum analyse on both program and logfile tables.  Estimates are 
more in line with reality now, but query still takes 10 seconds on v7.4 
and 10 ms on v7.3.  Function is marked as immutable and returns 
varchar(5).  I am wondering why the planner would choose a merge join 
(v7.4) as opposed to a nested loop (v7.3) given the small number of rows 
in the top level table (logfile) based upon the where clause (

L.air_date = '04/12/2002'::TIMESTAMP

)
there are typically only 30 rows per station/air_date.  What am I 
missing here?

Richard Huxton wrote:

>On Wednesday 26 November 2003 18:39, Roger Ging wrote:
>  
>
>>version 7.4 results:
>>
>>explain analyse SELECT L.row_id FROM music.logfile L LEFT JOIN
>>music.program P ON
>>music.fn_mri_id_no_program(P.mri_id_no) = L.program_id
>>WHERE  L.station = UPPER('kabc')::VARCHAR
>>AND L.air_date = '04/12/2002'::TIMESTAMP
>>AND P.cutoff_date IS NULL
>>ORDER BY L.chron_start,L.chron_end;
>>    
>>
>
>  
>
>>                ->  Seq Scan on program p  (cost=0.00..15192.35
>>rows=4335 width=20) (actual time=109.045..1955.882 rows=173998 loops=1)
>>    
>>
>
>The estimated number of rows here (4335) is *way* off (173998 actually). If 
>you only had 4335 rows, then this might be a more sensible plan.
>
>First step is to run:
>  VACUUM ANALYSE program;
>Then, check the definition of your function fn_mri_id_no_program() and make 
>sure it is marked immutable/stable (depending on what it does) and that it's 
>returning a varchar.
>
>
>  
>

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

* Re: Followup - expression (functional) index use in joins
  2003-11-26 18:39 Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
  2003-11-26 19:12 ` Re: Followup - expression (functional) index use in joins Richard Huxton <dev@archonet.com>
  2003-11-26 21:29   ` Re: Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
@ 2003-11-27 00:09     ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-12-01 17:14       ` Re: Followup - expression (functional) index use in joins Roger Ging <roger@musicreports.com>
  2003-12-01 17:31       ` Re: Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Tom Lane @ 2003-11-27 00:09 UTC (permalink / raw)
  To: Roger Ging <rging@paccomsys.com>; +Cc: pgsql-performance

Roger Ging <rging@paccomsys.com> writes:
> Ran vacuum analyse on both program and logfile tables.  Estimates are 
> more in line with reality now,

And they are what now?  You really can't expect to get useful help here
when you're being so miserly with the details ...

FWIW, I suspect you could force 7.4 to generate 7.3's plan by setting
enable_mergejoin to off (might have to also set enable_hashjoin to off,
if it then tries for a hash join).  7.3 could not even consider those
join types in this example, while 7.4 can.  The interesting question
from my perspective is why the planner is guessing wrong about the
relative costs of the plans.  EXPLAIN ANALYZE results with each type of
join forced would be useful to look at.

			regards, tom lane



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

* Re: Followup - expression (functional) index use in joins
  2003-11-26 18:39 Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
  2003-11-26 19:12 ` Re: Followup - expression (functional) index use in joins Richard Huxton <dev@archonet.com>
  2003-11-26 21:29   ` Re: Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
  2003-11-27 00:09     ` Re: Followup - expression (functional) index use in joins Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-12-01 17:14       ` Roger Ging <roger@musicreports.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Roger Ging @ 2003-12-01 17:14 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom,

Turning enable_hashjoin off made the query run as it had on v7.3.  We 
have worked around this by changing the index from a function call to a 
direct index on a new column with the results of the function maintained 
by a trigger.  Would there be performance issues from leaving 
enable_hashjoin off, or do you recomend enabling it, and working around 
function calls in indices?

See results below.  I was not sure if I was supposed to reply-all, or 
just to the list.  Sorry if the protocol is incorrect.



ppl=# explain analyse select title from music.program p
ppl-# join music.logfile l on
ppl-# l.program_id = music.fn_mri_id_no_program(p.mri_id_no)
ppl-# where l.air_date = '01/30/2001'
ppl-# and l.station = 'KABC';
                                                                       
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=69.89..19157.06 rows=2322 width=28) (actual 
time=500.905..1473.748 rows=242 loops=1)
   Hash Cond: ((music.fn_mri_id_no_program("outer".mri_id_no))::text = 
("inner".program_id)::text)
   ->  Seq Scan on program p  (cost=0.00..16888.98 rows=173998 width=40) 
(actual time=98.371..532.184 rows=173998 loops=1)
   ->  Hash  (cost=69.84..69.84 rows=17 width=9) (actual 
time=65.817..65.817 rows=0 loops=1)
         ->  Index Scan using idx_logfile_station_air_date on logfile l  
(cost=0.00..69.84 rows=17 width=9) (actual time=24.499..65.730 rows=32 
loops=1)
               Index Cond: (((station)::text = 'KABC'::text) AND 
(air_date = '2001-01-30 00:00:00'::timestamp without time zone))
 Total runtime: 1474.067 ms
(7 rows)

ppl=# set enable_mergejoin = false;
SET
ppl=# explain analyse select title from music.program p
ppl-# join music.logfile l on
ppl-# l.program_id = music.fn_mri_id_no_program(p.mri_id_no)
ppl-# where l.air_date = '01/30/2001'
ppl-# and l.station = 'KABC';
                                                                      
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=69.89..19157.06 rows=2322 width=28) (actual 
time=444.834..1428.815 rows=242 loops=1)
   Hash Cond: ((music.fn_mri_id_no_program("outer".mri_id_no))::text = 
("inner".program_id)::text)
   ->  Seq Scan on program p  (cost=0.00..16888.98 rows=173998 width=40) 
(actual time=105.977..542.870 rows=173998 loops=1)
   ->  Hash  (cost=69.84..69.84 rows=17 width=9) (actual 
time=1.197..1.197 rows=0 loops=1)
         ->  Index Scan using idx_logfile_station_air_date on logfile l  
(cost=0.00..69.84 rows=17 width=9) (actual time=0.574..1.151 rows=32 
loops=1)
               Index Cond: (((station)::text = 'KABC'::text) AND 
(air_date = '2001-01-30 00:00:00'::timestamp without time zone))
 Total runtime: 1429.111 ms
(7 rows)

ppl=# set enable_hashjoin = false;
SET
ppl=# explain analyse select title from music.program p
ppl-# join music.logfile l on
ppl-# l.program_id = music.fn_mri_id_no_program(p.mri_id_no)
ppl-# where l.air_date = '01/30/2001'
ppl-# and l.station = 'KABC';
                                                                      
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..58104.34 rows=2322 width=28) (actual 
time=0.480..5.357 rows=242 loops=1)
   ->  Index Scan using idx_logfile_station_air_date on logfile l  
(cost=0.00..69.84 rows=17 width=9) (actual time=0.176..0.754 rows=32 
loops=1)
         Index Cond: (((station)::text = 'KABC'::text) AND (air_date = 
'2001-01-30 00:00:00'::timestamp without time zone))
   ->  Index Scan using idx_program_mri_id_no_program on program p  
(cost=0.00..3400.74 rows=870 width=40) (actual time=0.041..0.127 rows=8 
loops=32)
         Index Cond: (("outer".program_id)::text = 
(music.fn_mri_id_no_program(p.mri_id_no))::text)
 Total runtime: 5.637 ms
(6 rows)


Tom Lane wrote:

>Roger Ging <rging@paccomsys.com> writes:
>  
>
>>Ran vacuum analyse on both program and logfile tables.  Estimates are 
>>more in line with reality now,
>>    
>>
>
>And they are what now?  You really can't expect to get useful help here
>when you're being so miserly with the details ...
>
>FWIW, I suspect you could force 7.4 to generate 7.3's plan by setting
>enable_mergejoin to off (might have to also set enable_hashjoin to off,
>if it then tries for a hash join).  7.3 could not even consider those
>join types in this example, while 7.4 can.  The interesting question
>from my perspective is why the planner is guessing wrong about the
>relative costs of the plans.  EXPLAIN ANALYZE results with each type of
>join forced would be useful to look at.
>
>			regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>  
>

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

* Re: Followup - expression (functional) index use in joins
  2003-11-26 18:39 Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
  2003-11-26 19:12 ` Re: Followup - expression (functional) index use in joins Richard Huxton <dev@archonet.com>
  2003-11-26 21:29   ` Re: Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
  2003-11-27 00:09     ` Re: Followup - expression (functional) index use in joins Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-12-01 17:31       ` Roger Ging <rging@paccomsys.com>
  2003-12-01 18:05         ` Re: Followup - expression (functional) index use in joins Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 7+ messages in thread

From: Roger Ging @ 2003-12-01 17:31 UTC (permalink / raw)
  To: pgsql-performance


Turning enable_hashjoin off made the query run as it had on v7.3.  We 
have worked around this by changing the index from a function call to a 
direct index on a new column with the results of the function maintained 
by a trigger.  Would there be performance issues from leaving 
enable_hashjoin off, or do you recomend enabling it, and working around 
function calls in indices?

See results below.



ppl=# explain analyse select title from music.program p
ppl-# join music.logfile l on
ppl-# l.program_id = music.fn_mri_id_no_program(p.mri_id_no)
ppl-# where l.air_date = '01/30/2001'
ppl-# and l.station = 'KABC';
                                                                       
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=69.89..19157.06 rows=2322 width=28) (actual 
time=500.905..1473.748 rows=242 loops=1)
   Hash Cond: ((music.fn_mri_id_no_program("outer".mri_id_no))::text = 
("inner".program_id)::text)
   ->  Seq Scan on program p  (cost=0.00..16888.98 rows=173998 width=40) 
(actual time=98.371..532.184 rows=173998 loops=1)
   ->  Hash  (cost=69.84..69.84 rows=17 width=9) (actual 
time=65.817..65.817 rows=0 loops=1)
         ->  Index Scan using idx_logfile_station_air_date on logfile l  
(cost=0.00..69.84 rows=17 width=9) (actual time=24.499..65.730 rows=32 
loops=1)
               Index Cond: (((station)::text = 'KABC'::text) AND 
(air_date = '2001-01-30 00:00:00'::timestamp without time zone))
 Total runtime: 1474.067 ms
(7 rows)

ppl=# set enable_mergejoin = false;
SET
ppl=# explain analyse select title from music.program p
ppl-# join music.logfile l on
ppl-# l.program_id = music.fn_mri_id_no_program(p.mri_id_no)
ppl-# where l.air_date = '01/30/2001'
ppl-# and l.station = 'KABC';
                                                                      
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=69.89..19157.06 rows=2322 width=28) (actual 
time=444.834..1428.815 rows=242 loops=1)
   Hash Cond: ((music.fn_mri_id_no_program("outer".mri_id_no))::text = 
("inner".program_id)::text)
   ->  Seq Scan on program p  (cost=0.00..16888.98 rows=173998 width=40) 
(actual time=105.977..542.870 rows=173998 loops=1)
   ->  Hash  (cost=69.84..69.84 rows=17 width=9) (actual 
time=1.197..1.197 rows=0 loops=1)
         ->  Index Scan using idx_logfile_station_air_date on logfile l  
(cost=0.00..69.84 rows=17 width=9) (actual time=0.574..1.151 rows=32 
loops=1)
               Index Cond: (((station)::text = 'KABC'::text) AND 
(air_date = '2001-01-30 00:00:00'::timestamp without time zone))
 Total runtime: 1429.111 ms
(7 rows)

ppl=# set enable_hashjoin = false;
SET
ppl=# explain analyse select title from music.program p
ppl-# join music.logfile l on
ppl-# l.program_id = music.fn_mri_id_no_program(p.mri_id_no)
ppl-# where l.air_date = '01/30/2001'
ppl-# and l.station = 'KABC';
                                                                      
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..58104.34 rows=2322 width=28) (actual 
time=0.480..5.357 rows=242 loops=1)
   ->  Index Scan using idx_logfile_station_air_date on logfile l  
(cost=0.00..69.84 rows=17 width=9) (actual time=0.176..0.754 rows=32 
loops=1)
         Index Cond: (((station)::text = 'KABC'::text) AND (air_date = 
'2001-01-30 00:00:00'::timestamp without time zone))
   ->  Index Scan using idx_program_mri_id_no_program on program p  
(cost=0.00..3400.74 rows=870 width=40) (actual time=0.041..0.127 rows=8 
loops=32)
         Index Cond: (("outer".program_id)::text = 
(music.fn_mri_id_no_program(p.mri_id_no))::text)
 Total runtime: 5.637 ms
(6 rows)


Tom Lane wrote:

>Roger Ging <rging@paccomsys.com> writes:
>  
>
>>Ran vacuum analyse on both program and logfile tables.  Estimates are 
>>more in line with reality now,
>>    
>>
>
>And they are what now?  You really can't expect to get useful help here
>when you're being so miserly with the details ...
>
>FWIW, I suspect you could force 7.4 to generate 7.3's plan by setting
>enable_mergejoin to off (might have to also set enable_hashjoin to off,
>if it then tries for a hash join).  7.3 could not even consider those
>join types in this example, while 7.4 can.  The interesting question
>from my perspective is why the planner is guessing wrong about the
>relative costs of the plans.  EXPLAIN ANALYZE results with each type of
>join forced would be useful to look at.
>
>			regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>  
>


Tom Lane wrote:

>Roger Ging <rging@paccomsys.com> writes:
>  
>
>>Ran vacuum analyse on both program and logfile tables.  Estimates are 
>>more in line with reality now,
>>    
>>
>
>And they are what now?  You really can't expect to get useful help here
>when you're being so miserly with the details ...
>
>FWIW, I suspect you could force 7.4 to generate 7.3's plan by setting
>enable_mergejoin to off (might have to also set enable_hashjoin to off,
>if it then tries for a hash join).  7.3 could not even consider those
>join types in this example, while 7.4 can.  The interesting question
>from my perspective is why the planner is guessing wrong about the
>relative costs of the plans.  EXPLAIN ANALYZE results with each type of
>join forced would be useful to look at.
>
>			regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>  
>

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

* Re: Followup - expression (functional) index use in joins
  2003-11-26 18:39 Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
  2003-11-26 19:12 ` Re: Followup - expression (functional) index use in joins Richard Huxton <dev@archonet.com>
  2003-11-26 21:29   ` Re: Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
  2003-11-27 00:09     ` Re: Followup - expression (functional) index use in joins Tom Lane <tgl@sss.pgh.pa.us>
  2003-12-01 17:31       ` Re: Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
@ 2003-12-01 18:05         ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2003-12-01 18:05 UTC (permalink / raw)
  To: Roger Ging <rging@paccomsys.com>; +Cc: pgsql-performance

Roger Ging <rging@paccomsys.com> writes:
> See results below.

Thanks for the report.  It seems the issue is that the estimate for the
number of matching rows is way off (870 vs 8):

>    ->  Index Scan using idx_program_mri_id_no_program on program p  
> (cost=0.00..3400.74 rows=870 width=40) (actual time=0.041..0.127 rows=8 
> loops=32)

which discourages the planner from using a nestloop.  I'm not sure we
can do much about this in the short term.  There's been some discussion
of keeping statistics about the values of functional indexes, which
would allow a better estimate to be made in this situation; but that
won't happen before 7.5 at the earliest.

> Turning enable_hashjoin off made the query run as it had on v7.3.  We 
> have worked around this by changing the index from a function call to a 
> direct index on a new column with the results of the function maintained 
> by a trigger.  Would there be performance issues from leaving 
> enable_hashjoin off, or do you recomend enabling it, and working around 
> function calls in indices?

Turning enable_hashjoin off globally would be a *really bad* idea IMHO.
The workaround with a derived column seems okay, though certainly a pain
in the neck.  Can you manage to turn off enable_hashjoin just for this
one query?  That might be the best short-term workaround.

			regards, tom lane



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


end of thread, other threads:[~2003-12-01 18:05 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-26 18:39 Followup - expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
2003-11-26 19:12 ` Richard Huxton <dev@archonet.com>
2003-11-26 21:29   ` Roger Ging <rging@paccomsys.com>
2003-11-27 00:09     ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-01 17:14       ` Roger Ging <roger@musicreports.com>
2003-12-01 17:31       ` Roger Ging <rging@paccomsys.com>
2003-12-01 18: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