X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 258629F9F27 for ; Tue, 18 Jul 2006 17:44:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 04134-07 for ; Tue, 18 Jul 2006 17:44:43 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from web55912.mail.re3.yahoo.com (web55912.mail.re3.yahoo.com [216.252.110.73]) by postgresql.org (Postfix) with SMTP id E9DD19F95E3 for ; Tue, 18 Jul 2006 17:44:41 -0300 (ADT) Received: (qmail 46377 invoked by uid 60001); 18 Jul 2006 16:02:53 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.ca; h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=zrCgVZz918rRrgX2TkNCnxN+bSTsJaKGm2nVAJVjEyMkv/s1bTxz7WFH/7OT7ayPMONV9G/AUGP1iwWfl196FPspieEMLkwjiifOVj50GZmxO17ANr0XOGb09YcXRqg6geRZFHjuItKVSFKLjESJA59ZJxlBp/VUD4oJqBoGeTw= ; Message-ID: <20060718160253.46375.qmail@web55912.mail.re3.yahoo.com> Received: from [207.164.182.8] by web55912.mail.re3.yahoo.com via HTTP; Tue, 18 Jul 2006 12:02:53 EDT Date: Tue, 18 Jul 2006 12:02:53 -0400 (EDT) From: Ioana Danes Subject: Re: Query plan issue when upgrading to postgres 8.14 (from To: Chris Cc: pgsql-performance@postgresql.org In-Reply-To: <44ADBE04.9010605@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1615721589-1153238573=:32061" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/188 X-Sequence-Number: 20048 --0-1615721589-1153238573=:32061 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Chris, Here are the results of my query for postgresql 8.0.3 and 8.1.4. For postgresql 8.1.4 there are 2 results, one for test table having the same indexes as in 8.0.3 and the second one for a new index on test table by (testtype,testid) that will speed up my query. This last index will fix my problem for this particular query. In the Test table there are 19,494,826 records and 11,090 records have testtype = 1455. The data on both servers is identical. And on both servers I run vacuum analyze prior executing this queries. As it can be seen the result in postgresql 8.1.4 is very slow and I am wondering why is that. Bug, missing configuration, ... 1. Result on Postgresql 8.0.3: ------------------------------------- # explain analyze select max(TESTID) from TEST where TESTTYPE = 1455; Aggregate (cost=391.56..391.56 rows=1 width=8) (actual time=94.707..94.711 rows=1 loops=1) -> Index Scan using ix_test_testtype on test (cost=0.00..355.18 rows=14551 width=8) (actual time=0.036..51.089 rows=11090 loops=1) Index Cond: (testtype = 1455) Total runtime: 94.778 ms (4 rows) # select max(TESTID) from TEST where TESTTYPE = 1455; max ---------- 18527829 (1 row) Time: 13.447 ms 2. Result on Postgresql 8.1.4 (with the same indexes as in 8.0.3): ------------------------------------------------------------------------------------------ Result (cost=32.78..32.79 rows=1 width=0) (actual time=1865.406..1865.408 rows=1 loops=1) InitPlan -> Limit (cost=0.00..32.78 rows=1 width=8) (actual time=1865.378..1865.381 rows=1 loops=1) -> Index Scan Backward using pk_testid on test (cost=0.00..464069.25 rows=14155 width=8) (actual time=1865.371..1865.371 rows=1 loops=1) Filter: ((testid IS NOT NULL) AND (testtype = 1455)) Total runtime: 1865.522 ms (6 rows) # select max(TESTID) from TEST where TESTTYPE = 1455; max ---------- 18527829 Time: 1858.076 ms 3. Result on Postgresql 8.1.4 (after creating an index by testtype, testid ): ----------------------------------------------------------------------------------------------------- # explain analyze select max(TESTID) from TEST where TESTTYPE = 1455; Result (cost=1.71..1.72 rows=1 width=0) (actual time=0.069..0.070 rows=1 loops=1) InitPlan -> Limit (cost=0.00..1.71 rows=1 width=8) (actual time=0.055..0.056 rows=1 loops=1) -> Index Scan Backward using ix_test2 on test (cost=0.00..24248.92 rows=14155 width=8) (actual time=0.050..0.050 rows=1 loops=1) Index Cond: (testtype = 1455) Filter: (testid IS NOT NULL) Total runtime: 0.159 ms # select max(TESTID) from TEST where TESTTYPE = 1455; max ---------- 18527829 Time: 1.029 ms Thank you very much, Ioana Danes Chris wrote: You've left out the best details. Post an 'explain analyze' from both versions, and don't cut anything out :) I'm guessing postgres is seeing an index on the table is faster because it doesn't think you have many rows in the table. How many are there, and have you done an analyze of the table after loading the data in? -- Postgresql & php tutorials http://www.designmagick.com/ ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster --------------------------------- Now you can have a huge leap forward in email: get the new Yahoo! Mail. --0-1615721589-1153238573=:32061 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Chris,

Here are the results of my query for postgresql 8.0.3 and 8.1.4. For postgresql 8.1.4 there are 2 results, one for test table having the same indexes as in 8.0.3 and the second one for a new index on test table by (testtype,testid) that will speed up my query. This last index will fix my problem for this particular query.

 In the Test table there are 19,494,826 records and 11,090 records have testtype = 1455. The data on both servers is identical. And on both servers I run vacuum analyze prior executing this queries.

As it can be seen the result in postgresql 8.1.4 is very slow and I am wondering why is that. Bug, missing configuration, ...

1. Result on Postgresql 8.0.3:
-------------------------------------
# explain analyze select max(TESTID) from TEST where TESTTYPE = 1455;

 Aggregate  (cost=391.56..391.56 rows=1 width=8) (actual time=94.707..94.711 rows=1 loops=1)
   ->  Index Scan using ix_test_testtype on test  (cost=0.00..355.18 rows=14551 width=8) (actual time=0.036..51.089 rows=11090 loops=1)
         Index Cond: (testtype = 1455)
 Total runtime: 94.778 ms
(4 rows)

# select max(TESTID) from TEST where TESTTYPE = 1455;

   max
----------
 18527829
(1 row)

Time: 13.447 ms


2. Result on Postgresql 8.1.4 (with the same indexes as in 8.0.3):
------------------------------------------------------------------------------------------
 Result  (cost=32.78..32.79 rows=1 width=0) (actual time=1865.406..1865.408 rows=1 loops=1)
   InitPlan
     ->  Limit  (cost=0.00..32.78 rows=1 width=8) (actual time=1865.378..1865.381 rows=1 loops=1)
           ->  Index Scan Backward using pk_testid on test  (cost=0.00..464069.25 rows=14155 width=8) (actual time=1865.371..1865.371 rows=1 loops=1)
                 Filter: ((testid IS NOT NULL) AND (testtype = 1455))
 Total runtime: 1865.522 ms
(6 rows)

# select max(TESTID) from TEST where TESTTYPE = 1455;
 
   max
----------
 18527829

Time: 1858.076 ms


3. Result on Postgresql 8.1.4 (after creating an index by testtype, testid ):
-----------------------------------------------------------------------------------------------------
# explain analyze select max(TESTID) from TEST where TESTTYPE = 1455;
 Result  (cost=1.71..1.72 rows=1 width=0) (actual time=0.069..0.070 rows=1 loops=1)
   InitPlan
     ->  Limit  (cost=0.00..1.71 rows=1 width=8) (actual time=0.055..0.056 rows=1 loops=1)
           ->  Index Scan Backward using ix_test2 on test  (cost=0.00..24248.92 rows=14155 width=8) (actual time=0.050..0.050 rows=1 loops=1)
                 Index Cond: (testtype = 1455)
                 Filter: (testid IS NOT NULL)
 Total runtime: 0.159 ms

# select max(TESTID) from TEST where TESTTYPE = 1455;

   max
----------
 18527829

Time: 1.029 ms


Thank you very much,
Ioana Danes

Chris <dmagick@gmail.com> wrote:

You've left out the best details. Post an 'explain analyze' from both
versions, and don't cut anything out :)

I'm guessing postgres is seeing an index on the table is faster because
it doesn't think you have many rows in the table. How many are there,
and have you done an analyze of the table after loading the data in?

--
Postgresql & php tutorials
http://www.designmagick.com/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Now you can have a huge leap forward in email: get the new Yahoo! Mail. --0-1615721589-1153238573=:32061--