pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy the difference in plans ??
13+ messages / 5 participants
[nested] [flat]
* Why the difference in plans ??
@ 2006-09-15 08:39 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 13+ messages in thread
From: Joost Kraaijeveld @ 2006-09-15 08:39 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have two table: customers and salesorders. salesorders have a foreign
key to the customer
If I run this query:
SELECT
salesOrders.objectid,
salesOrders.ordernumber,
salesOrders.orderdate,
customers.objectid,
customers.customernumber,
customers.lastname
FROM prototype.salesorders
INNER JOIN prototype.customers ON (
customers.objectid = salesorders.customer
)
where
lastname ilike 'Boonk'
order by ordernumber asc LIMIT 1
WITHOUT "LIMIT 1" this query plan is executed (EXPLAIN ANALYZE):
Sort (cost=41811.90..41812.78 rows=353 width=103) (actual time=623.855..623.867 rows=7 loops=1)
Sort Key: salesorders.ordernumber
-> Nested Loop (cost=2.15..41796.96 rows=353 width=103) (actual time=0.166..623.793 rows=7 loops=1)
-> Seq Scan on customers (cost=0.00..21429.44 rows=118 width=55) (actual time=0.037..623.325 rows=5 loops=1)
Filter: (lastname ~~* 'Boonk'::text)
-> Bitmap Heap Scan on salesorders (cost=2.15..172.06 rows=44 width=88) (actual time=0.075..0.079 rows=1 loops=5)
Recheck Cond: ("outer".objectid = salesorders.customer)
-> Bitmap Index Scan on orders_customer (cost=0.00..2.15 rows=44 width=0) (actual time=0.066..0.066 rows=1 loops=5)
Index Cond: ("outer".objectid = salesorders.customer)
Total runtime: 624.051 ms
With the limit this query plan is used (EXPLAIN ANALYZE):
Limit (cost=0.00..18963.24 rows=1 width=103) (actual time=18404.730..18404.732 rows=1 loops=1)
-> Nested Loop (cost=0.00..6694025.41 rows=353 width=103) (actual time=18404.723..18404.723 rows=1 loops=1)
-> Index Scan using prototype_orders_ordernumber on salesorders (cost=0.00..37263.14 rows=1104381 width=88) (actual time=26.715..1862.408 rows=607645 loops=1)
-> Index Scan using pk_prototype_customers on customers (cost=0.00..6.02 rows=1 width=55) (actual time=0.023..0.023 rows=0 loops=607645)
Index Cond: (customers.objectid = "outer".customer)
Filter: (lastname ~~* 'Boonk'::text)
Total runtime: 18404.883 ms
Both tables are freshly fully vacuumed analyzed.
Why the difference and can I influence the result so that the first
query plan (which is the fastest) is actually used in both cases (I
would expect that the limit would be done after the sort?)?
TIA
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Why the difference in plans ??
@ 2006-09-15 14:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2006-09-15 14:08 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-performance
"Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> writes:
> Why the difference and can I influence the result so that the first
> query plan (which is the fastest) is actually used in both cases (I
> would expect that the limit would be done after the sort?)?
It likes the second plan because 6694025.41/353 < 41812.78. It would
probably be right, too, if the number of matching rows were indeed 353,
but it seems there are only 7. Try increasing your statistics target
and re-analyzing.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Why the difference in plans ??
@ 2006-09-15 16:05 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Joost Kraaijeveld @ 2006-09-15 16:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Fri, 2006-09-15 at 10:08 -0400, Tom Lane wrote:
> but it seems there are only 7. Try increasing your statistics target
> and re-analyzing.
Do you mean with "increasing my statistics target" changing the value of
"default_statistics_target = 10" to a bigger number? If so, changing it
to 900 did not make any difference (PostgreSQL restarted, vacuumed
analysed etc).
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Why the difference in plans ??
@ 2006-09-15 16:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2006-09-15 16:17 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-performance
Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
> Do you mean with "increasing my statistics target" changing the value of
> "default_statistics_target = 10" to a bigger number? If so, changing it
> to 900 did not make any difference (PostgreSQL restarted, vacuumed
> analysed etc).
Hm, did the "353" rowcount estimate not change at all?
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Why the difference in plans ?
@ 2008-03-06 00:27 Dave Cramer <pg@fastcrypt.com>
0 siblings, 2 replies; 13+ messages in thread
From: Dave Cramer @ 2008-03-06 00:27 UTC (permalink / raw)
To: pgsql-performance
Below I have two almost identical queries. Strangely enough the one
that uses the index is slower ???
explain analyze select uid from user_profile where
lower(firstname)='angie' and extract(year from age('2008-02-26
02:50:31.382', dob)) >= 18 and extract(year from age('2008-02-26
02:50:31.382', dob)) <= 68 and image1 is not null and profileprivacy=1
and isactive='t' order by name asc limit 250;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=166423.90..166423.93 rows=11 width=17) (actual
time=1033.634..1034.137 rows=129 loops=1)
-> Sort (cost=166423.90..166423.93 rows=11 width=17) (actual
time=1033.631..1033.811 rows=129 loops=1)
Sort Key: name
-> Seq Scan on user_profile (cost=0.00..166423.71 rows=11
width=17) (actual time=46.730..1032.994 rows=129 loops=1)
Filter: ((lower((firstname)::text) = 'angie'::text)
AND (date_part('year'::text, age('2008-02-26 02:50:31.382'::timestamp
without time zone, dob)) >= 18::double precision) AND
(date_part('year'::text, age('2008-02-26 02:50:31.382'::timestamp
without time zone, dob)) <= 68::double precision) AND (image1 IS NOT
NULL) AND (profileprivacy = 1) AND isactive)
Total runtime: 1034.334 ms
(6 rows)
jnj=# explain analyze select uid from user_profile where
lower(firstname)='angie' and dob <= '1990-03-05 15:17:29.537' and dob
>= '1940-03-05 15:17:29.537' and image1 is not null and
profileprivacy=1 and isactive='t' order by name asc limit 250;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..113963.92 rows=250 width=17) (actual
time=230.326..4688.607 rows=129 loops=1)
-> Index Scan using user_profile_name_key on user_profile
(cost=0.00..460414.23 rows=1010 width=17) (actual
time=230.322..4688.174 rows=129 loops=1)
Filter: ((lower((firstname)::text) = 'angie'::text) AND (dob
<= '1990-03-05 15:17:29.537'::timestamp without time zone) AND (dob >=
'1940-03-05 15:17:29.537'::timestamp without time zone) AND (image1 IS
NOT NULL) AND (profileprivacy = 1) AND isactive)
Total runtime: 4688.906 ms
(4 rows)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Why the difference in plans ?
@ 2008-03-06 17:26 Josh Berkus <josh@agliodbs.com>
parent: Dave Cramer <pg@fastcrypt.com>
1 sibling, 2 replies; 13+ messages in thread
From: Josh Berkus @ 2008-03-06 17:26 UTC (permalink / raw)
To: pgsql-performance; +Cc: Dave Cramer <pg@fastcrypt.com>
Dave,
> Below I have two almost identical queries. Strangely enough the one
> that uses the index is slower ???
My first guess would be that records are highly correlated by DOB and not at
all by name. However, it would help if you supplied both the index
definitions and what changed between the two queries to cause the index to be
used.
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Why the difference in plans ?
@ 2008-03-06 18:56 Dave Cramer <pg@fastcrypt.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 13+ messages in thread
From: Dave Cramer @ 2008-03-06 18:56 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
On 6-Mar-08, at 12:26 PM, Josh Berkus wrote:
> Dave,
>
>> Below I have two almost identical queries. Strangely enough the one
>> that uses the index is slower ???
>
> My first guess would be that records are highly correlated by DOB
> and not at
> all by name. However, it would help if you supplied both the index
> definitions and what changed between the two queries to cause the
> index to be
> used.
The two queries were run 2 seconds apart, there were no changes
between. I'll get the index definitions.
Dave
>
>
> --
> Josh Berkus
> PostgreSQL @ Sun
> San Francisco
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
> )
> To make changes to your subscription:
> http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Why the difference in plans ?
@ 2008-03-06 22:10 Stephen Denne <Stephen.Denne@datamail.co.nz>
parent: Dave Cramer <pg@fastcrypt.com>
1 sibling, 1 reply; 13+ messages in thread
From: Stephen Denne @ 2008-03-06 22:10 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: pgsql-performance
Dave Cramer wrote:
> I have two almost identical queries. Strangely enough the one
> that uses the index is slower ???
The index scan is being used so that it can retrieve the rows in the name order.
It expects that if it was to retrieve every row via the index, it would get about 1010 rows that matched the filter, and it knows it can stop after 250, so assuming the matching rows are evenly distributed it thinks it can stop after having read only a quarter of the rows.
However only 129 rows matched. Consequently it had to read every row in the table anyway, seeking a fair bit as the read order was specified by the index rather than in sequential order, and it also had to read the index. These extra costs were much larger than reading the lot sequentially, and sorting 129 resulting rows.
The first query picked a sequential scan as it thought it was only going to get 11 results, so was expecting that the limit wasn't going to come into play, and that every row would have to be read anyway.
Regards,
Stephen Denne.
Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way.
__________________________________________________________________
This email has been scanned by the DMZGlobal Business Quality
Electronic Messaging Suite.
Please see http://www.dmzglobal.com/services/bqem.htm for details.
__________________________________________________________________
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Why the difference in plans ?
@ 2008-03-07 02:16 Dave Cramer <pg@fastcrypt.com>
parent: Stephen Denne <Stephen.Denne@datamail.co.nz>
0 siblings, 1 reply; 13+ messages in thread
From: Dave Cramer @ 2008-03-07 02:16 UTC (permalink / raw)
To: Stephen Denne <Stephen.Denne@datamail.co.nz>; +Cc: pgsql-performance
On 6-Mar-08, at 5:10 PM, Stephen Denne wrote:
> Dave Cramer wrote:
>> I have two almost identical queries. Strangely enough the one
>> that uses the index is slower ???
>
> The index scan is being used so that it can retrieve the rows in the
> name order.
> It expects that if it was to retrieve every row via the index, it
> would get about 1010 rows that matched the filter, and it knows it
> can stop after 250, so assuming the matching rows are evenly
> distributed it thinks it can stop after having read only a quarter
> of the rows.
>
> However only 129 rows matched. Consequently it had to read every row
> in the table anyway, seeking a fair bit as the read order was
> specified by the index rather than in sequential order, and it also
> had to read the index. These extra costs were much larger than
> reading the lot sequentially, and sorting 129 resulting rows.
>
> The first query picked a sequential scan as it thought it was only
> going to get 11 results, so was expecting that the limit wasn't
> going to come into play, and that every row would have to be read
> anyway.
>
The strange thing of course is that the data is exactly the same for
both runs, the tables have not been changed between runs, and I did
them right after another. Even more strange is that the seq scan is
faster than the index scan.
Dave
> Regards,
> Stephen Denne.
>
> Disclaimer:
> At the Datamail Group we value team commitment, respect,
> achievement, customer focus, and courage. This email with any
> attachments is confidential and may be subject to legal privilege.
> If it is not intended for you please advise by reply immediately,
> destroy it and do not copy, disclose or use it in any way.
>
> __________________________________________________________________
> This email has been scanned by the DMZGlobal Business Quality
> Electronic Messaging Suite.
> Please see http://www.dmzglobal.com/services/bqem.htm for details.
> __________________________________________________________________
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Why the difference in plans ?
@ 2008-03-07 02:30 Stephen Denne <Stephen.Denne@datamail.co.nz>
parent: Dave Cramer <pg@fastcrypt.com>
0 siblings, 1 reply; 13+ messages in thread
From: Stephen Denne @ 2008-03-07 02:30 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: pgsql-performance
> The strange thing of course is that the data is exactly the same for
> both runs, the tables have not been changed between runs, and I did
> them right after another. Even more strange is that the seq scan is
> faster than the index scan.
It is not strange at all, since both queries read ALL the rows in your table, checking each and every row to see whether it matched your predicates.
The sequential scan read them in the order they are on the disk, meaning your disk didn't have to seek as much (assuming low file fragmentation).
The index scan again reads all the rows in your table, but reads them in the order they were in the index, which is probably quite different from the order that they are on the disk, so the disk had to seek a lot. In addition, it had to read the index.
Taking some wild guesses about the distribution of your data, I'd hazard a guess that this specific query could be sped up a great deal by creating an index on lower(firstname).
Regards,
Stephen.
Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way.
__________________________________________________________________
This email has been scanned by the DMZGlobal Business Quality
Electronic Messaging Suite.
Please see http://www.dmzglobal.com/services/bqem.htm for details.
__________________________________________________________________
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Why the difference in plans ?
@ 2008-03-07 11:21 Dave Cramer <pg@fastcrypt.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 13+ messages in thread
From: Dave Cramer @ 2008-03-07 11:21 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
Josh,
On 6-Mar-08, at 12:26 PM, Josh Berkus wrote:
> Dave,
>
>> Below I have two almost identical queries. Strangely enough the one
>> that uses the index is slower ???
>
> My first guess would be that records are highly correlated by DOB
> and not at
> all by name. However, it would help if you supplied both the index
> definitions and what changed between the two queries to cause the
> index to be
> used.
Indexes:
"user_profile_pkey" PRIMARY KEY, btree (uid) CLUSTER
"user_profile_name_idx" UNIQUE, btree (name varchar_pattern_ops)
"user_profile_name_key" UNIQUE, btree (name)
"user_profile_uploadcode_key" UNIQUE, btree (uploadcode)
"user_profile_active_idx" btree (isactive)
"user_profile_areacode_index" btree (areacode)
"user_profile_gender_idx" btree (gender)
and nothing changed between runs.
Dave
>
>
> --
> Josh Berkus
> PostgreSQL @ Sun
> San Francisco
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
> )
> To make changes to your subscription:
> http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Why the difference in plans ?
@ 2008-03-07 11:21 Dave Cramer <pg@fastcrypt.com>
parent: Stephen Denne <Stephen.Denne@datamail.co.nz>
0 siblings, 0 replies; 13+ messages in thread
From: Dave Cramer @ 2008-03-07 11:21 UTC (permalink / raw)
To: Stephen Denne <Stephen.Denne@datamail.co.nz>; +Cc: pgsql-performance
On 6-Mar-08, at 9:30 PM, Stephen Denne wrote:
>> The strange thing of course is that the data is exactly the same for
>> both runs, the tables have not been changed between runs, and I did
>> them right after another. Even more strange is that the seq scan is
>> faster than the index scan.
>
> It is not strange at all, since both queries read ALL the rows in
> your table, checking each and every row to see whether it matched
> your predicates.
>
> The sequential scan read them in the order they are on the disk,
> meaning your disk didn't have to seek as much (assuming low file
> fragmentation).
>
> The index scan again reads all the rows in your table, but reads
> them in the order they were in the index, which is probably quite
> different from the order that they are on the disk, so the disk had
> to seek a lot. In addition, it had to read the index.
>
OK, that makes sense.
So given that the predicates are essentially the same why would the
planner decide to use or not use the index ?
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Why the difference in plans ?
@ 2008-03-08 01:23 Josh Berkus <josh@agliodbs.com>
parent: Dave Cramer <pg@fastcrypt.com>
0 siblings, 0 replies; 13+ messages in thread
From: Josh Berkus @ 2008-03-08 01:23 UTC (permalink / raw)
To: pgsql-performance; +Cc: Dave Cramer <pg@fastcrypt.com>
Dave,
> "user_profile_pkey" PRIMARY KEY, btree (uid) CLUSTER
> "user_profile_name_idx" UNIQUE, btree (name varchar_pattern_ops)
> "user_profile_name_key" UNIQUE, btree (name)
> "user_profile_uploadcode_key" UNIQUE, btree (uploadcode)
> "user_profile_active_idx" btree (isactive)
> "user_profile_areacode_index" btree (areacode)
> "user_profile_gender_idx" btree (gender)
You need to change one of the name indexes to a functional index on
lower(firstname). That'll speed the query up considerably.
I'm still puzzled as to why the index is being used at all in the 2nd
query, as it seems very unlikely to work out, but the above is the
practical solution to your problem.
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2008-03-08 01:23 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-15 08:39 Why the difference in plans ?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-09-15 14:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-15 16:05 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-09-15 16:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 00:27 Why the difference in plans ? Dave Cramer <pg@fastcrypt.com>
2008-03-06 17:26 ` Re: Why the difference in plans ? Josh Berkus <josh@agliodbs.com>
2008-03-06 18:56 ` Re: Why the difference in plans ? Dave Cramer <pg@fastcrypt.com>
2008-03-07 11:21 ` Re: Why the difference in plans ? Dave Cramer <pg@fastcrypt.com>
2008-03-08 01:23 ` Re: Why the difference in plans ? Josh Berkus <josh@agliodbs.com>
2008-03-06 22:10 ` Re: Why the difference in plans ? Stephen Denne <Stephen.Denne@datamail.co.nz>
2008-03-07 02:16 ` Re: Why the difference in plans ? Dave Cramer <pg@fastcrypt.com>
2008-03-07 02:30 ` Re: Why the difference in plans ? Stephen Denne <Stephen.Denne@datamail.co.nz>
2008-03-07 11:21 ` Re: Why the difference in plans ? Dave Cramer <pg@fastcrypt.com>
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