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@xxxxxxxxxxxxxx so that your
> message can get through to the mailing list cleanly
>
>
>
