pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gary Doades <gpd@gpdnet.co.uk>
To: pgsql-performance@postgresql.org
Subject: Re: planner/optimizer question
Date: Thu, 29 Apr 2004 23:31:00 +0100
Message-ID: <40919034.20317.153A0B6F@localhost> (raw)
In-Reply-To: <17175.1083275671@sss.pgh.pa.us>
References: <409172FA.1509.14C7E00E@localhost>

On 29 Apr 2004 at 17:54, Tom Lane wrote:

> "Gary Doades" <gpd@gpdnet.co.uk> writes:
> > In this example the statistics don't matter.
> 
> Don't they?
> 
> A prior poster mentioned that he thought MSSQL tries to keep all its
> indexes in memory.  I wonder whether you are giving Postgres a fair
> chance to do the same.  What postgresql.conf settings are you using?
> 
> 			regards, tom lane

As far as I understand it the statistics only contribute to determining the query plan. 
Once the access methods are determined, the stats don't matter during the running of 
the query.

I believe I have given Postgres exactly the same chance. The data is small enough to fit 
into RAM (all the tables in the query add up to around 50meg) and I executed the query 
several times to get a consistent figure for the explain analyze. 

Having picked out an index scan as being the highest time user I concentrated on that in 
this case and compared the same index scan on MSSQL. At least MSSQL reported it as 
an index scan on the same index for the same number of rows.

There was nothing wrong with the query plan that Postgres used. As far as I could see it 
was probably the best one to use, it just physically took longer than the same access 
plan on MSSQL.

The query and plan are included below, the main thing I was looking at was the index 
scan on staff_booking_pkey being 676ms long.

The only postgresql.conf parameters changed from the default are:

shared_buffers = 3000	
sort_mem = 4096
effective_cache_size = 15000
default_statistics_target = 100

There was no disk IO (above the small background IO) during the final run of the query 
as reported by vmstat (Task Mangler on Windows).

SELECT B.CONTRACT_ID,SUM(R.DURATION+1)/60.0 AS SUMDUR FROM 
SEARCH_REQT_RESULT TSR
JOIN STAFF_BOOKING B ON (B.STAFF_ID = TSR.STAFF_ID)
JOIN ORDER_REQT R ON (R.REQT_ID = B.REQT_ID)
JOIN BOOKING_PLAN BP ON (BP.BOOKING_ID = B.BOOKING_ID) AND 
BP.BOOKING_DATE BETWEEN '2004-04-12' AND '2004-04-18' AND 
TSR.SEARCH_ID = 8 GROUP BY B.CONTRACT_ID

QUERY PLAN
HashAggregate  (cost=11205.80..11209.81 rows=401 width=6) (actual 
time=1179.729..1179.980 rows=50 loops=1)
  ->  Nested Loop  (cost=326.47..11203.79 rows=401 width=6) (actual 
time=39.700..1177.149 rows=652 loops=1)
        ->  Hash Join  (cost=326.47..9990.37 rows=401 width=8) (actual 
time=39.537..1154.807 rows=652 loops=1)
              Hash Cond: ("outer".staff_id = "inner".staff_id)
              ->  Merge Join  (cost=320.39..9885.06 rows=3809 width=12) (actual 
time=38.316..1143.953 rows=4079 loops=1)
                    Merge Cond: ("outer".booking_id = "inner".booking_id)
                    ->  Index Scan using staff_booking_pkey on staff_booking b  
(cost=0.00..8951.94 rows=222612 width=16) (actual time=0.218..676.219 rows=222609 
loops=1)
                    ->  Sort  (cost=320.39..329.91 rows=3808 width=4) (actual 
time=26.225..32.754 rows=4079 loops=1)
                          Sort Key: bp.booking_id
                          ->  Index Scan using booking_plan_idx2 on booking_plan bp  
(cost=0.00..93.92 rows=3808 width=4) (actual time=0.223..14.186 rows=4079 loops=1)
                                Index Cond: ((booking_date >= '2004-04-12'::date) AND 
(booking_date <= '2004-04-18'::date))
              ->  Hash  (cost=5.59..5.59 rows=193 width=4) (actual time=1.139..1.139 
rows=0 loops=1)
                    ->  Index Scan using fk_idx_search_reqt_result on search_reqt_result tsr  
(cost=0.00..5.59 rows=193 width=4) (actual time=0.213..0.764 rows=192 loops=1)
                          Index Cond: (search_id = 8)
        ->  Index Scan using order_reqt_pkey on order_reqt r  (cost=0.00..3.01 rows=1 
width=6) (actual time=0.023..0.025 rows=1 loops=652)
              Index Cond: (r.reqt_id = "outer".reqt_id)
Total runtime: 1181.239 ms


Cheers,
Gary.

view thread (37+ messages)  latest in thread

Message-ID: <40919034.20317.153A0B6F@localhost>
Permalink:  ../40919034.20317.153A0B6F@localhost/
Also on:    postgresql.org/message-id/40919034.20317.153A0B6F@localhost

 · 

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: gpd@gpdnet.co.uk
  Subject: Re: planner/optimizer question
  In-Reply-To: <40919034.20317.153A0B6F@localhost>

* 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