From: Ioana Danes <ioanasoftware@yahoo.ca>
To: Ioana Danes <ioanasoftware@yahoo.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query plan issue when upgrading to postgres 8.14 (from
Date: Thu, 27 Jul 2006 14:55:06 -0400 (EDT)
Message-ID: <20060727185507.9604.qmail@web55902.mail.re3.yahoo.com> (raw)
In-Reply-To: <20060718160253.46375.qmail@web55912.mail.re3.yahoo.com>
Hi everyone,
I posted this question some time ago and I did not get any answer so here I am again.
Does anyone now what the problem is with the following select when upgrading to postgresql 8.1.4 the query plan does not use the indexes as in postgresql 8.0.3.
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 in advance,
Ioana
---------------------------------
Be smarter than spam. See how smart SpamGuard is at giving junk email the boot with the All-new Yahoo! Mail
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: ioanasoftware@yahoo.ca
Subject: Re: Query plan issue when upgrading to postgres 8.14 (from
In-Reply-To: <20060727185507.9604.qmail@web55902.mail.re3.yahoo.com>
* 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