X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B1797D1D61F for ; Mon, 1 Dec 2003 17:31:40 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 43508-08 for ; Mon, 1 Dec 2003 13:31:10 -0400 (AST) Received: from pcs-1.paccomsys.com (208.225.nwc.net [207.151.225.208]) by svr1.postgresql.org (Postfix) with ESMTP id CB4E5D1B486 for ; Mon, 1 Dec 2003 13:31:07 -0400 (AST) Received: from paccomsys.com (mail.musicreports.com [64.161.179.34]) by pcs-1.paccomsys.com (8.12.5/8.12.5) with ESMTP id hB1HAN74031563 for ; Mon, 1 Dec 2003 09:10:24 -0800 Message-ID: <3FCB7ADA.8020101@paccomsys.com> Date: Mon, 01 Dec 2003 09:31:06 -0800 From: Roger Ging User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3) Gecko/20030708 Debian/1.3-4.lindows43 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Followup - expression (functional) index use in joins References: <3FC4F360.2090609@paccomsys.com> <200311261912.01286.dev@archonet.com> <3FC51B2C.2030700@paccomsys.com> <18733.1069891765@sss.pgh.pa.us> In-Reply-To: <18733.1069891765@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------080104020701070107090204" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/13 X-Sequence-Number: 4873 This is a multi-part message in MIME format. --------------080104020701070107090204 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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 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 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 > > --------------080104020701070107090204 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
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
  
--------------080104020701070107090204--