pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4)
6+ messages / 3 participants
[nested] [flat]
* Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4)
@ 2006-07-06 20:06 Ioana Danes <ioanasoftware@yahoo.ca>
2006-07-07 01:51 ` Re: Query plan issue when upgrading to postgres 8.14 (from Chris <dmagick@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ioana Danes @ 2006-07-06 20:06 UTC (permalink / raw)
To: pgsql-performance
I have a problem with a query that in postgres 7.4 and 8.12 has an acceptable response time but in postgres 8.14 is very slow.
This is the table I use:
create table TEST (
TESTID INT8 not null,
TESTTYPE INT4 null,
constraint PK_TESTID primary key (TESTID));
create index IX_TEST_TESTTYPE on TEST (TESTTYPE);
And this is the query with the problem:
explain select max(TESTID) from TEST where TESTTYPE = 1577;
The query plan in postgres 7.4 and 8.12 is using the index by TESTTYPE field, which is what I want in this case.
QUERY PLAN
Aggregate (cost=25.97..25.97 rows=1 width=8)
-> Index Scan using ix_test_testtype on test (cost=0.00..25.95 rows=9 width=8)
Index Cond: (testtype = 1577)
With postgres 8.14 the query plan uses the primary key PK_TESTID with filter by TESTTYPE, which it takes almost 10 minutes to execute:
QUERY PLAN
Limit (cost=0.00..41.46 rows=1 width=8)
-> Index Scan Backward using pk_testid on test (cost=…)
Filter: ((testid IS NOT NULL) and (testtype = 1577))
When replacing the index
create index IX_TEST_TESTTYPE on TEST (TESTTYPE);
with
create index IX_TEST_TESTTYPE on TEST (TESTTYPE, TESTID);
the query plan uses this index and the execution of this select is extremely fast.
From what I can see, the query plan for 8.14 is using a index scan by the field used with max() function with a filter by the field in where condition.
Should not the query plan use an index scan by the field in where condition (which in my case is a small range) and come up with the max value in that range?
Is this a bug, am I missing a configuration step or this is how it is supposed to work?
Thank you very much,
Ioana
---------------------------------
Make free worldwide PC-to-PC calls. Try the new Yahoo! Canada Messenger with Voice
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query plan issue when upgrading to postgres 8.14 (from
2006-07-06 20:06 Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4) Ioana Danes <ioanasoftware@yahoo.ca>
@ 2006-07-07 01:51 ` Chris <dmagick@gmail.com>
2006-07-18 16:02 ` Re: Query plan issue when upgrading to postgres 8.14 (from Ioana Danes <ioanasoftware@yahoo.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Chris @ 2006-07-07 01:51 UTC (permalink / raw)
To: Ioana Danes <ioanasoftware@yahoo.ca>; +Cc: pgsql-performance
Ioana Danes wrote:
> I have a problem with a query that in postgres 7.4 and 8.12 has an
> acceptable response time but in postgres 8.14 is very slow.
>
> This is the table I use:
> *
> create* *table* TEST (
> TESTID INT8 *not* *null*,
> TESTTYPE INT4 *null*,
> *constraint* PK_TESTID *primary* *key* (TESTID));
> *create* *index* IX_TEST_TESTTYPE *on* TEST (TESTTYPE);
>
> And this is the query with the problem:
>
> *explain select* *max*(TESTID) *from* TEST *where* TESTTYPE = 1577;
>
> The query plan in postgres 7.4 and 8.12 is using the index by TESTTYPE
> field, which is what I want in this case.
>
> QUERY PLAN
> Aggregate (cost=25.97..25.97 rows=1 width=8)
> -> Index Scan using ix_test_testtype on test (cost=0.00..25.95
> rows=9 width=8)
> Index Cond: (testtype = 1577)
>
>
> With postgres 8.14 the query plan uses the primary key PK_TESTID with
> filter by TESTTYPE, which it takes almost 10 minutes to execute:
>
> QUERY PLAN
> Limit (cost=0.00..41.46 rows=1 width=8)
> -> Index Scan Backward using pk_testid on test (cost=…)
> Filter: ((testid IS NOT NULL) and (testtype = 1577))
>
> When replacing the index
> *create* *index* IX_TEST_TESTTYPE *on* TEST (TESTTYPE);
> with
> *create* *index* IX_TEST_TESTTYPE *on* TEST (TESTTYPE, TESTID);
> the query plan uses this index and the execution of this select is
> extremely fast.
>
> From what I can see, the query plan for 8.14 is using a index scan by
> the field used with max() function with a filter by the field in where
> condition.
> Should not the query plan use an index scan by the field in where
> condition (which in my case is a small range) and come up with the max
> value in that range?
>
> Is this a bug, am I missing a configuration step or this is how it is
> supposed to work?
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query plan issue when upgrading to postgres 8.14 (from
2006-07-06 20:06 Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4) Ioana Danes <ioanasoftware@yahoo.ca>
2006-07-07 01:51 ` Re: Query plan issue when upgrading to postgres 8.14 (from Chris <dmagick@gmail.com>
@ 2006-07-18 16:02 ` Ioana Danes <ioanasoftware@yahoo.ca>
2006-07-27 18:55 ` Re: Query plan issue when upgrading to postgres 8.14 (from Ioana Danes <ioanasoftware@yahoo.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Ioana Danes @ 2006-07-18 16:02 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query plan issue when upgrading to postgres 8.14 (from
2006-07-06 20:06 Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4) Ioana Danes <ioanasoftware@yahoo.ca>
2006-07-07 01:51 ` Re: Query plan issue when upgrading to postgres 8.14 (from Chris <dmagick@gmail.com>
2006-07-18 16:02 ` Re: Query plan issue when upgrading to postgres 8.14 (from Ioana Danes <ioanasoftware@yahoo.ca>
@ 2006-07-27 18:55 ` Ioana Danes <ioanasoftware@yahoo.ca>
2006-07-27 22:22 ` Re: Query plan issue when upgrading to postgres 8.14 (from Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Ioana Danes @ 2006-07-27 18:55 UTC (permalink / raw)
To: Ioana Danes <ioanasoftware@yahoo.ca>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query plan issue when upgrading to postgres 8.14 (from
2006-07-06 20:06 Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4) Ioana Danes <ioanasoftware@yahoo.ca>
2006-07-07 01:51 ` Re: Query plan issue when upgrading to postgres 8.14 (from Chris <dmagick@gmail.com>
2006-07-18 16:02 ` Re: Query plan issue when upgrading to postgres 8.14 (from Ioana Danes <ioanasoftware@yahoo.ca>
2006-07-27 18:55 ` Re: Query plan issue when upgrading to postgres 8.14 (from Ioana Danes <ioanasoftware@yahoo.ca>
@ 2006-07-27 22:22 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2006-07-27 22:22 UTC (permalink / raw)
To: Ioana Danes <ioanasoftware@yahoo.ca>; +Cc: pgsql-performance
Ioana Danes <ioanasoftware@yahoo.ca> writes:
> 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.
The planner doesn't have enough information about the correlation
between testtype and testid to guess that the index-driven max()
optimization doesn't work well in this case. But I see you've
already found the solution ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4)
@ 2006-07-06 21:34 Ioana Danes <ioanasoftware@yahoo.ca>
0 siblings, 0 replies; 6+ messages in thread
From: Ioana Danes @ 2006-07-06 21:34 UTC (permalink / raw)
To: pgsql-performance
I have a problem with a query that in postgres 7.4 and 8.12 has an acceptable response time but in postgres 8.14 is very slow.
This is the table I use:
create table TEST (
TESTID INT8 not null,
TESTTYPE INT4 null,
constraint PK_TESTID primary key (TESTID));
create index IX_TEST_TESTTYPE on TEST (TESTTYPE);
And this is the query with the problem:
explain select max(TESTID) from TEST where TESTTYPE = 1577;
The query plan in postgres 7.4 and 8.12 is using the index by TESTTYPE field, which is what I want in this case.
QUERY PLAN
Aggregate (cost=25.97..25.97 rows=1 width=8)
-> Index Scan using ix_test_testtype on test (cost=0.00..25.95 rows=9 width=8)
Index Cond: (testtype = 1577)
With postgres 8.14 the query plan uses the primary key PK_TESTID with filter by TESTTYPE, which it takes almost 10 minutes to execute:
QUERY PLAN
Limit (cost=0.00..41.46 rows=1 width=8)
-> Index Scan Backward using pk_testid on test (cost=…)
Filter: ((testid IS NOT NULL) and (testtype = 1577))
When replacing the index
create index IX_TEST_TESTTYPE on TEST (TESTTYPE);
with
create index IX_TEST_TESTTYPE on TEST (TESTTYPE, TESTID);
the query plan uses this index and the execution of this select is extremely fast.
From what I can see, the query plan for 8.14 is using a index scan by the field used with max() function with a filter by the field in the where condition.
Should not the query plan use an index scan by the field in where condition (which in my case is a small range) and come up with the max value in that range?
Is this a bug, am I missing a configuration step or this is how it is supposed to work?
Thank you very much,
Ioana
---------------------------------
All new Yahoo! Mail -
---------------------------------
Get a sneak peak at messages with a handy reading pane.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-07-27 22:22 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-06 20:06 Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4) Ioana Danes <ioanasoftware@yahoo.ca>
2006-07-07 01:51 ` Re: Query plan issue when upgrading to postgres 8.14 (from Chris <dmagick@gmail.com>
2006-07-18 16:02 ` Re: Query plan issue when upgrading to postgres 8.14 (from Ioana Danes <ioanasoftware@yahoo.ca>
2006-07-27 18:55 ` Re: Query plan issue when upgrading to postgres 8.14 (from Ioana Danes <ioanasoftware@yahoo.ca>
2006-07-27 22:22 ` Re: Query plan issue when upgrading to postgres 8.14 (from Tom Lane <tgl@sss.pgh.pa.us>
2006-07-06 21:34 Query plan issue when upgrading to postgres 8.14 (from postgres 8.12 or 7.4) Ioana Danes <ioanasoftware@yahoo.ca>
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