X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E74DAD1B341 for ; Thu, 5 Aug 2004 07:07:37 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 87105-04 for ; Thu, 5 Aug 2004 10:07:38 +0000 (GMT) Received: from linda-1.paradise.net.nz (bm-1a.paradise.net.nz [202.0.58.20]) by svr1.postgresql.org (Postfix) with ESMTP id 87B6DD1B340 for ; Thu, 5 Aug 2004 07:07:34 -0300 (ADT) Received: from smtp-3.paradise.net.nz (smtp-3b.paradise.net.nz [202.0.32.212]) by linda-1.paradise.net.nz (Paradise.net.nz) with ESMTP id <0I1Y002VYXGM5Y@linda-1.paradise.net.nz> for pgsql-performance@postgresql.org; Thu, 05 Aug 2004 22:07:35 +1200 (NZST) Received: from coretech.co.nz (218-101-45-230.paradise.net.nz [218.101.45.230]) by smtp-3.paradise.net.nz (Postfix) with ESMTP id 15C28AE0F7; Thu, 05 Aug 2004 22:07:34 +1200 (NZST) Date: Thu, 05 Aug 2004 22:09:58 +1200 From: Mark Kirkwood Subject: Re: [GENERAL] Tuning queries on large database In-reply-to: <200408050816.i758GxO21560@mu.meteo.fr> To: Valerie Schneider DSI/DEV Cc: chriskl@familyhealth.com.au, pgsql-performance@postgresql.org Message-id: <41120776.4090401@coretech.co.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=us-ascii Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.6) Gecko/20040429 References: <200408050816.i758GxO21560@mu.meteo.fr> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/62 X-Sequence-Number: 7698 I am guessing that Oracle can satisfy Q4 entirely via index access, whereas Pg has to visit the table as well. Having said that, a few partial indexes may be worth trying out on data.num_poste (say 10 or so), this won't help the table access but could lower the index cost. If you combine this with loading the data in num_poste order (or run CLUSTER), you may get closer to Oracle's time for this query. regards Mark Valerie Schneider DSI/DEV wrote: >For my different queries, it's better but less performant than oracle : > > oracle PG yesterday(numeric) PG today(integer/real) > >Q4 28s 17m20s 6m47s > > > >Q4 : bench=> explain analyze select 'Q4',count(*) from data where num_poste >between 600 and 625; > QUERY PLAN >-------------------------------------------------------------------------------- > Aggregate (cost=14086174.57..14086174.57 rows=1 width=0) (actual >time=428235.024..428235.025 rows=1 loops=1) > -> Index Scan using pk_data on data (cost=0.00..14076910.99 rows=3705431 >width=0) (actual time=45.283..424634.826 rows=3252938 loops=1) > Index Cond: ((num_poste >= 600) AND (num_poste <= 625)) > Total runtime: 428235.224 ms >(4 rows) > >Thanks for all, Valerie. > > >