X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 2D92A9DC841 for ; Tue, 17 Jan 2006 18:03:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 37842-07 for ; Tue, 17 Jan 2006 18:03:38 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id E2C2D9DC80F for ; Tue, 17 Jan 2006 18:03:33 -0400 (AST) Received: from plain.rackshack.net (unknown [66.98.130.45]) by svr4.postgresql.org (Postfix) with ESMTP id 644395AF057 for ; Tue, 17 Jan 2006 22:03:36 +0000 (GMT) Received: from fatchubby (cpe-071-070-134-248.nc.res.rr.com [71.70.134.248]) by plain.rackshack.net (8.12.10/8.12.5) with SMTP id k0HLsl3x011535 for ; Tue, 17 Jan 2006 16:54:47 -0500 From: J@Planeti.Biz Message-ID: <05c301c61bb1$d88c6a00$681e140a@fatchubby> To: Subject: Multiple Order By Criteria Date: Tue, 17 Jan 2006 17:03:27 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_05C0_01C61B87.EF7142B0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2527 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.551 required=5 tests=[HTML_MESSAGE=0.001, NO_REAL_NAME=0.55] X-Spam-Score: 0.551 X-Spam-Level: X-Archive-Number: 200601/268 X-Sequence-Number: 16746 This is a multi-part message in MIME format. ------=_NextPart_000_05C0_01C61B87.EF7142B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm trying to query a table with 250,000+ rows. My query requires I = provide 5 colums in my "order by" clause: select column from table=20 where=20 column >=3D '2004-3-22 0:0:0' order by=20 ds.receipt desc, ds.carrier_id asc, ds.batchnum asc, encounternum asc, ds.encounter_id ASC limit 100 offset 0 I have an index built for each of these columns in my order by clause. = This query takes an unacceptable amount of time to execute. Here are the = results of the explain: Limit (cost=3D229610.78..229611.03 rows=3D100 width=3D717) -> Sort (cost=3D229610.78..230132.37 rows=3D208636 width=3D717) Sort Key: receipt, carrier_id, batchnum, encounternum, = encounter_id -> Seq Scan on detail_summary ds (cost=3D0.00..22647.13 = rows=3D208636 width=3D717) Filter: (receipt >=3D '2004-03-22'::date) When I have the order by just have 1 criteria, it's fine (just = ds.receipt DESC) Limit (cost=3D0.00..177.71 rows=3D100 width=3D717) -> Index Scan Backward using detail_summary_receipt_id_idx on = detail_summary ds (cost=3D0.00..370756.84 rows=3D208636 width=3D717) Index Cond: (receipt >=3D '2004-03-22'::date) I've increased my work_mem to up to 256meg with no speed increase. I = think there's something here I just don't understand. How do I make this go fast ? ------=_NextPart_000_05C0_01C61B87.EF7142B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I'm trying to query a table with = 250,000+ rows. My=20 query requires I provide 5 colums in my "order by" clause:
 
select
   column
from table
where
 column >=3D '2004-3-22 = 0:0:0'
order by=20
    ds.receipt = desc,
    ds.carrier_id = asc,
    ds.batchnum = asc,
    encounternum = asc,
    ds.encounter_id = ASC
limit 100 offset 0
 
I have an index built for each of these = columns in=20 my order by clause. This query takes an unacceptable amount of time to = execute.=20 Here are the results of the explain:
 
Limit  = (cost=3D229610.78..229611.03 rows=3D100=20 width=3D717)
  ->  Sort  = (cost=3D229610.78..230132.37=20 rows=3D208636 width=3D717)
        = Sort Key:=20 receipt, carrier_id, batchnum, encounternum,=20 encounter_id
        ->  = Seq Scan=20 on detail_summary ds  (cost=3D0.00..22647.13 rows=3D208636=20 width=3D717)
         &nb= sp;   =20 Filter: (receipt >=3D '2004-03-22'::date)
 
When I have the order by just have 1 = criteria, it's=20 fine (just ds.receipt DESC)
 
Limit  (cost=3D0.00..177.71 = rows=3D100=20 width=3D717)
  ->  Index Scan Backward using=20 detail_summary_receipt_id_idx on detail_summary ds  = (cost=3D0.00..370756.84=20 rows=3D208636 width=3D717)
        = Index Cond:=20 (receipt >=3D '2004-03-22'::date)
 
I've increased my work_mem to up to = 256meg with no=20 speed increase. I think there's something here I just don't=20 understand.
 
How do I make this go fast ?
 
 
 
 
------=_NextPart_000_05C0_01C61B87.EF7142B0--