pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedweird query plan
3+ messages / 2 participants
[nested] [flat]
* weird query plan
@ 2007-06-06 03:45 weiping <laserlist@pgsqldb.com>
2007-06-06 03:59 ` Re: weird query plan weiping <laserlist@pgsqldb.com>
2007-06-06 14:40 ` Re: weird query plan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: weiping @ 2007-06-06 03:45 UTC (permalink / raw)
To: pgsql-performance
I have a table:
webdigest=# \d wd_urlusermaps
表 "public.wd_urlusermaps"
字段名 | 类型 | 修饰词
---------+-----------------------------+-------------------------------------------------------------
id | integer | not null default nextval('wd_urlusermaps_id_seq'::regclass)
urlid | integer | not null
tag | character varying(512) |
title | character varying(512) |
summary | character varying(1024) |
comment | character varying(1024) |
ctime | timestamp without time zone |
mtime | timestamp without time zone |
share | smallint |
userid | integer |
import | smallint | default 0
索引:
"wd_urlusermaps_pkey" PRIMARY KEY, btree (id) CLUSTER
"urlusermaps_urlid_userid" UNIQUE, btree (urlid, userid)
"urlusermaps_urlid" btree (urlid)
"urlusermaps_userid" btree (userid)
"wd_urlusermaps_ctime_idx" btree (ctime)
"wd_urlusermaps_share_idx" btree ("share")
and target statistic set to 1000, and two different query plan:
webdigest=# explain analyze select A.id as
fav_id,A.urlid,A.tag,A.title,A.summary,A.comment,A.ctime,A.share from
wd_urlusermaps A where share =1 and A.userid='219177' ORDER BY A.id DESC
limit 20 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..4932.56 rows=20 width=96) (actual
time=730.461..2374.435 rows=20 loops=1)
-> Index Scan Backward using wd_urlusermaps_pkey on wd_urlusermaps a
(cost=0.00..269810.77 rows=1094 width=96) (actual time=730.456..2374.367
rows=20 loops=1)
Filter: (("share" = 1) AND (userid = 219177))
Total runtime: 2374.513 ms
(4 rows)
webdigest=# explain analyze select A.id as
fav_id,A.urlid,A.tag,A.title,A.summary,A.comment,A.ctime,A.share from
wd_urlusermaps A where share =1 and A.userid='219177' ORDER BY A.id DESC
limit 40 ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=6805.77..6805.87 rows=40 width=96) (actual time=5.731..5.905
rows=40 loops=1)
-> Sort (cost=6805.77..6808.50 rows=1094 width=96) (actual
time=5.726..5.785 rows=40 loops=1)
Sort Key: id
-> Index Scan using urlusermaps_userid on wd_urlusermaps a
(cost=0.00..6750.55 rows=1094 width=96) (actual time=0.544..5.616
rows=41 loops=1)
Index Cond: (userid = 219177)
Filter: ("share" = 1)
Total runtime: 6.013 ms
(7 rows)
the userid=219177 got 2000+ record and around 40 shared=1, why above 2 query
shows so much difference?
any hint would be greatly appreciated.
-laser
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: weird query plan
2007-06-06 03:45 weird query plan weiping <laserlist@pgsqldb.com>
@ 2007-06-06 03:59 ` weiping <laserlist@pgsqldb.com>
1 sibling, 0 replies; 3+ messages in thread
From: weiping @ 2007-06-06 03:59 UTC (permalink / raw)
To: pgsql-performance
sorry, forgot to mention our version, it's postgresql 8.2.3
-laser
> I have a table:
> webdigest=# \d wd_urlusermaps
> 表 "public.wd_urlusermaps"
> 字段名 | 类型 | 修饰词
> ---------+-----------------------------+-------------------------------------------------------------
> id | integer | not null default nextval('wd_urlusermaps_id_seq'::regclass)
> urlid | integer | not null
> tag | character varying(512) |
> title | character varying(512) |
> summary | character varying(1024) |
> comment | character varying(1024) |
> ctime | timestamp without time zone |
> mtime | timestamp without time zone |
> share | smallint |
> userid | integer |
> import | smallint | default 0
> 索引:
> "wd_urlusermaps_pkey" PRIMARY KEY, btree (id) CLUSTER
> "urlusermaps_urlid_userid" UNIQUE, btree (urlid, userid)
> "urlusermaps_urlid" btree (urlid)
> "urlusermaps_userid" btree (userid)
> "wd_urlusermaps_ctime_idx" btree (ctime)
> "wd_urlusermaps_share_idx" btree ("share")
>
> and target statistic set to 1000, and two different query plan:
>
> webdigest=# explain analyze select A.id as
> fav_id,A.urlid,A.tag,A.title,A.summary,A.comment,A.ctime,A.share from
> wd_urlusermaps A where share =1 and A.userid='219177' ORDER BY A.id DESC
> limit 20 ;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..4932.56 rows=20 width=96) (actual
> time=730.461..2374.435 rows=20 loops=1)
> -> Index Scan Backward using wd_urlusermaps_pkey on wd_urlusermaps a
> (cost=0.00..269810.77 rows=1094 width=96) (actual time=730.456..2374.367
> rows=20 loops=1)
> Filter: (("share" = 1) AND (userid = 219177))
> Total runtime: 2374.513 ms
> (4 rows)
>
> webdigest=# explain analyze select A.id as
> fav_id,A.urlid,A.tag,A.title,A.summary,A.comment,A.ctime,A.share from
> wd_urlusermaps A where share =1 and A.userid='219177' ORDER BY A.id DESC
> limit 40 ;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=6805.77..6805.87 rows=40 width=96) (actual time=5.731..5.905
> rows=40 loops=1)
> -> Sort (cost=6805.77..6808.50 rows=1094 width=96) (actual
> time=5.726..5.785 rows=40 loops=1)
> Sort Key: id
> -> Index Scan using urlusermaps_userid on wd_urlusermaps a
> (cost=0.00..6750.55 rows=1094 width=96) (actual time=0.544..5.616
> rows=41 loops=1)
> Index Cond: (userid = 219177)
> Filter: ("share" = 1)
> Total runtime: 6.013 ms
> (7 rows)
>
> the userid=219177 got 2000+ record and around 40 shared=1, why above 2 query
> shows so much difference?
>
> any hint would be greatly appreciated.
>
> -laser
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: weird query plan
2007-06-06 03:45 weird query plan weiping <laserlist@pgsqldb.com>
@ 2007-06-06 14:40 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2007-06-06 14:40 UTC (permalink / raw)
To: weiping <laserlist@pgsqldb.com>; +Cc: pgsql-performance
weiping <laserlist@pgsqldb.com> writes:
> -> Index Scan using urlusermaps_userid on wd_urlusermaps a
> (cost=0.00..6750.55 rows=1094 width=96) (actual time=0.544..5.616
> rows=41 loops=1)
> Index Cond: (userid = 219177)
> Filter: ("share" = 1)
> the userid=219177 got 2000+ record and around 40 shared=1, why above 2 query
> shows so much difference?
Probably because the rowcount estimate is so far off (1094 vs 41).
Possibly boosting the statistics target would help.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-06-06 14:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-06 03:45 weird query plan weiping <laserlist@pgsqldb.com>
2007-06-06 03:59 ` weiping <laserlist@pgsqldb.com>
2007-06-06 14:40 ` Tom Lane <tgl@sss.pgh.pa.us>
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