weird query plan - Mailing list pgsql-performance
From | weiping |
---|---|
Subject | weird query plan |
Date | |
Msg-id | 46662DE7.4030602@pgsqldb.com Whole thread Raw |
Responses |
Re: weird query plan
Re: weird query plan |
List | 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
pgsql-performance by date: