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 69E2A9DCC5B for ; Wed, 21 Dec 2005 20:35:01 -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 55932-02 for ; Wed, 21 Dec 2005 20:35:03 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from web35513.mail.mud.yahoo.com (web35513.mail.mud.yahoo.com [66.163.179.137]) by postgresql.org (Postfix) with SMTP id 06D569DCC57 for ; Wed, 21 Dec 2005 20:34:57 -0400 (AST) Received: (qmail 98885 invoked by uid 60001); 22 Dec 2005 00:35:01 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=D53F19SI2w/T9xcCS2fh0lFcFTHZJCjD5VagiSjKn0nqoMLq5o2wvxC5Kv6Ki3fOQfF0nqvfLMJkNGzzGJMoBISiVuqh7eD1D2zzhvQjpg+5bWt+Sxw9TyVCbE+JnCb6RPRdf2E2+sxBLJpq8gjDWtcmiXkgjMMAUuuTZyDlesc= ; Message-ID: <20051222003501.98879.qmail@web35513.mail.mud.yahoo.com> Received: from [200.101.229.54] by web35513.mail.mud.yahoo.com via HTTP; Thu, 22 Dec 2005 00:35:00 GMT Date: Thu, 22 Dec 2005 00:35:00 +0000 (GMT) From: Carlos Benkendorf Subject: Re: ORDER BY costs To: Tom Lane , pgsql-performance@postgresql.org In-Reply-To: <15455.1135193975@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1250591123-1135211700=:98678" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.48 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479, HTML_MESSAGE=0.001] X-Spam-Score: 0.48 X-Spam-Level: X-Archive-Number: 200512/548 X-Sequence-Number: 16369 --0-1250591123-1135211700=:98678 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit I restored the table in another database and repeated the analyze again with original column definitions (numeric): With order by: Sort (cost=212634.30..213032.73 rows=159374 width=897) (actual time=9286.817..9865.030 rows=167710 loops=1) Sort Key: anocalc, cadastro, codvencto, parcela -> Index Scan using pk_arript, pk_arript, pk_arript, pk_arript on arript (cost=0.00..126604.64 rows=159374 width=897) (actual time=0.152..1062.664 rows=167710 loops=1) Index Cond: (((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto = 0::numeric) AND (parcela >= 0::numeric)) OR ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto > 0::numeric)) OR ((anocalc = 2005::numeric) AND (cadastro > 19::numeric)) OR (anocalc > 2005::numeric)) Total runtime: 10086.884 ms (5 rows) Without order by: Index Scan using pk_arript, pk_arript, pk_arript, pk_arript on arript (cost=0.00..126604.64 rows=159374 width=897) (actual time=0.154..809.566 rows=167710 loops=1) Index Cond: (((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto = 0::numeric) AND (parcela >= 0::numeric)) OR ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto > 0::numeric)) OR ((anocalc = 2005::numeric) AND (cadastro > 19::numeric)) OR (anocalc > 2005::numeric)) Total runtime: 894.218 ms (3 rows) Then I recreated the table and changed the primary key column type definitions to smallint, integer and bigint. CREATE TABLE arript ( anocalc smallint NOT NULL, cadastro integer NOT NULL, codvencto smallint NOT NULL, parcela smallint NOT NULL, inscimob character varying(18) NOT NULL, codvencto2 smallint NOT NULL, parcela2 smallint NOT NULL, codpropr bigint NOT NULL, dtaven integer NOT NULL, anocalc2 smallint, dtabase integer, vvt numeric(14,2), vvp numeric(14,2), ... ... Now the new analyze: With order by: Sort (cost=180430.98..180775.10 rows=137649 width=826) (actual time=4461.524..5000.707 rows=167710 loops=1) Sort Key: anocalc, cadastro, codvencto, parcela -> Index Scan using pk_arript, pk_arript, pk_arript, pk_arript on arript (cost=0.00..111126.93 rows=137649 width=826) (actual time=0.142..763.255 rows=167710 loops=1) Index Cond: (((anocalc = 2005) AND (cadastro = 19) AND (codvencto = 0) AND (parcela >= 0)) OR ((anocalc = 2005) AND (cadastro = 19) AND (codvencto > 0)) OR ((anocalc = 2005) AND (cadastro > 19)) OR (anocalc > 2005)) Total runtime: 5222.729 ms (5 rows) Without order by: Index Scan using pk_arript, pk_arript, pk_arript, pk_arript on arript (cost=0.00..111126.93 rows=137649 width=826) (actual time=0.135..505.250 rows=167710 loops=1) Index Cond: (((anocalc = 2005) AND (cadastro = 19) AND (codvencto = 0) AND (parcela >= 0)) OR ((anocalc = 2005) AND (cadastro = 19) AND (codvencto > 0)) OR ((anocalc = 2005) AND (cadastro > 19)) OR (anocalc > 2005)) Total runtime: 589.528 ms (3 rows) Total runtime summary: Primary key columns defined with integer/smallint/bigint and select with order by: 5222.729 ms Primary key columns defined with integer/smallint/bigint and select without order by: 589.528 ms Primary key columns defined with numeric and select with order by: 10086.884 ms Primary key columns defined with numeric and select without order by: 894.218 ms Using order by and integer/smallint/bigint (5222.729) is almost half total runtime of select over numeric columns (10086.884) but is still 6 x more from the numbers of the original select (without order by and number columns=894.218). Is there something more that could be done? Planner cost constants? Thanks very much in advance! Benkendorf --------------------------------- Yahoo! doce lar. Faça do Yahoo! sua homepage. --0-1250591123-1135211700=:98678 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
I restored the table in another database and repeated the analyze again with original column definitions (numeric):
 
With order by:
Sort  (cost=212634.30..213032.73 rows=159374 width=897) (actual time=9286.817..9865.030 rows=167710 loops=1)
   Sort Key: anocalc, cadastro, codvencto, parcela
   ->  Index Scan using pk_arript, pk_arript, pk_arript, pk_arript on arript  (cost=0.00..126604.64 rows=159374 width=897) (actual time=0.152..1062.664 rows=167710 loops=1)
         Index Cond: (((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto = 0::numeric) AND (parcela >= 0::numeric)) OR ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto > 0::numeric)) OR ((anocalc = 2005::numeric) AND (cadastro > 19::numeric)) OR (anocalc > 2005::numeric))
 Total runtime: 10086.884 ms
(5 rows)
 
Without order by:
 Index Scan using pk_arript, pk_arript, pk_arript, pk_arript on arript  (cost=0.00..126604.64 rows=159374 width=897) (actual time=0.154..809.566 rows=167710 loops=1)
   Index Cond: (((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto = 0::numeric) AND (parcela >= 0::numeric)) OR ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto > 0::numeric)) OR ((anocalc = 2005::numeric) AND (cadastro > 19::numeric)) OR (anocalc > 2005::numeric))
 Total runtime: 894.218 ms
(3 rows)


Then I recreated the table and changed the primary key column type definitions to smallint, integer and bigint.
 
CREATE TABLE arript (
    anocalc smallint     NOT NULL,
    cadastro integer      NOT NULL,
    codvencto smallint     NOT NULL,
    parcela smallint     NOT NULL,
    inscimob character varying(18) NOT NULL,
    codvencto2 smallint     NOT NULL,
    parcela2 smallint     NOT NULL,
    codpropr bigint        NOT NULL,
    dtaven integer      NOT NULL,
    anocalc2 smallint,
    dtabase integer,
    vvt numeric(14,2),
    vvp numeric(14,2),
...
...
 
Now the new analyze:
 
With order by:
 Sort  (cost=180430.98..180775.10 rows=137649 width=826) (actual time=4461.524..5000.707 rows=167710 loops=1)
   Sort Key: anocalc, cadastro, codvencto, parcela
   ->&n bsp; Index Scan using pk_arript, pk_arript, pk_arript, pk_arript on arript  (cost=0.00..111126.93 rows=137649 width=826) (actual time=0.142..763.255 rows=167710 loops=1)
         Index Cond: (((anocalc = 2005) AND (cadastro = 19) AND (codvencto = 0) AND (parcela >= 0)) OR ((anocalc = 2005) AND (cadastro = 19) AND (codvencto > 0)) OR ((anocalc = 2005) AND (cadastro > 19)) OR (anocalc > 2005))
 Total runtime: 5222.729 ms
(5 rows)
 
 
Without order by:
 Index Scan using pk_arript, pk_arript, pk_arript, pk_arript on arript  (cost=0.00..111126.93 rows=137649 width=826) (actual time=0.135..505.250 rows=167710 loops=1)
   Index Cond: (((anocalc = 2005) AND (cadastro = 19) AND (codvencto = 0) AND (parcela >= 0)) OR ((anocalc = 2005) AND (cadastro = 19) AND (codvencto > 0)) OR ((anocalc = 2005) AND (cadastro > 19)) OR (anocalc > 2005))
 Total runtime: 589.528 ms
(3 rows)
Total runtime summary:
Primary key columns defined with integer/smallint/bigint and select with order by: 5222.729 ms
Primary key columns defined with integer/smallint/bigint and select without order by: 589.528 ms
Primary key columns defined with numeric and select with order by: 10086.884 ms
Primary key columns defined with numeric and select without order by: 894.218 ms
 
Using order by and integer/smallint/bigint (5222.729) is almost half total runtime of select over numeric columns (10086.884) but is still 6 x more from the numbers of the original select (without order by and number columns=894.218).
 
Is there something more that could be done? Planner cost constants?
 
Thanks very much in advance!< /DIV>
 
Benkendorf
 
 

Yahoo! doce lar. Faça do Yahoo! sua homepage. --0-1250591123-1135211700=:98678--