关于相同的语句limit不同导致不同的性能

大家好：

SELECT "rate_plan_sub"."id", "rate_plan_sub"."status", "rate_plan_sub"."rate_date", "rate_plan_sub"."price", "rate_plan_sub"."is_need_guarantee", "rate_plan_sub"."breakfast", "rate_plan_sub"."number_of_breakfast" FROM "rate_plan_sub" INNER JOIN "hotel_rateplansubs" ON ( "rate_plan_sub"."id" = "hotel_rateplansubs"."rateplansub_id" ) WHERE ("hotel_rateplansubs"."hotel_id" = '10255071' AND "rate_plan_sub"."rate_date" = '2014-07-18' ) ORDER BY "rate_plan_sub"."price" ASC LIMIT 1;

"Limit (cost=0.00..31581.95 rows=1 width=53)"
" -> Nested Loop (cost=0.00..4231981.19 rows=134 width=53)"
" -> Index Scan using rate_plan_sub_price on rate_plan_sub (cost=0.00..1282749.97 rows=402233 width=53)"
" Filter: (rate_date = '2014-07-18'::date)"
" -> Index Scan using hotel_rateplansubs_rateplansub_id_like on hotel_rateplansubs (cost=0.00..7.32 rows=1 width=33)"
" Index Cond: ((rateplansub_id)::text = (rate_plan_sub.id)::text)"
" Filter: ((hotel_id)::text = '10255071'::text)"

上面这个语句需要20秒的时间。

SELECT "rate_plan_sub"."id", "rate_plan_sub"."status", "rate_plan_sub"."rate_date", "rate_plan_sub"."price", "rate_plan_sub"."is_need_guarantee", "rate_plan_sub"."breakfast", "rate_plan_sub"."number_of_breakfast" FROM "rate_plan_sub" INNER JOIN "hotel_rateplansubs" ON ( "rate_plan_sub"."id" = "hotel_rateplansubs"."rateplansub_id" ) WHERE ("hotel_rateplansubs"."hotel_id" = '10255071' AND "rate_plan_sub"."rate_date" = '2014-07-18' ) ORDER BY "rate_plan_sub"."price" ASC LIMIT 2;

"Limit (cost=59518.56..59518.56 rows=2 width=53)"
" -> Sort (cost=59518.56..59518.89 rows=134 width=53)"
" Sort Key: rate_plan_sub.price"
" -> Nested Loop (cost=107.56..59517.22 rows=134 width=53)"
" -> Bitmap Heap Scan on hotel_rateplansubs (cost=107.56..12849.50 rows=3754 width=33)"
" Recheck Cond: ((hotel_id)::text = '10255071'::text)"
" -> Bitmap Index Scan on hotel_rateplansubs_hotel_id (cost=0.00..106.62 rows=3754 width=0)"
" Index Cond: ((hotel_id)::text = '10255071'::text)"
" -> Index Scan using rate_plan_sub_pkey on rate_plan_sub (cost=0.00..12.42 rows=1 width=53)"
" Index Cond: ((id)::text = (hotel_rateplansubs.rateplansub_id)::text)"
" Filter: (rate_date = '2014-07-18'::date)"

上面这个语句仅仅需要10毫秒，两个语句就只是limit不同而已。

下面这些是建表语句：

-- Table: hotel_rateplansubs

-- DROP TABLE hotel_rateplansubs;

CREATE TABLE hotel_rateplansubs
(
id serial NOT NULL,
hotel_id character varying(32) NOT NULL,
rateplansub_id character varying(32) NOT NULL,
CONSTRAINT hotel_rateplansubs_pkey PRIMARY KEY (id ),
CONSTRAINT hotel_id_refs_uid_bf3b5c97 FOREIGN KEY (hotel_id)
REFERENCES hotel (uid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT rateplansub_id_refs_id_9e3ac872 FOREIGN KEY (rateplansub_id)
REFERENCES rate_plan_sub (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT hotel_rateplansubs_hotel_id_rateplansub_id_key UNIQUE (hotel_id , rateplansub_id )
)
WITH (
OIDS=FALSE
);
ALTER TABLE hotel_rateplansubs
OWNER TO postgres;

-- Index: hotel_rateplansubs_hotel_id

-- DROP INDEX hotel_rateplansubs_hotel_id;

CREATE INDEX hotel_rateplansubs_hotel_id
ON hotel_rateplansubs
USING btree
(hotel_id COLLATE pg_catalog."default" );

-- Index: hotel_rateplansubs_hotel_id_like

-- DROP INDEX hotel_rateplansubs_hotel_id_like;

CREATE INDEX hotel_rateplansubs_hotel_id_like
ON hotel_rateplansubs
USING btree
(hotel_id COLLATE pg_catalog."default" varchar_pattern_ops);

-- Index: hotel_rateplansubs_rateplansub_id

-- DROP INDEX hotel_rateplansubs_rateplansub_id;

CREATE INDEX hotel_rateplansubs_rateplansub_id
ON hotel_rateplansubs
USING btree
(rateplansub_id COLLATE pg_catalog."default" );

-- Index: hotel_rateplansubs_rateplansub_id_like

-- DROP INDEX hotel_rateplansubs_rateplansub_id_like;

CREATE INDEX hotel_rateplansubs_rateplansub_id_like
ON hotel_rateplansubs
USING btree
(rateplansub_id COLLATE pg_catalog."default" varchar_pattern_ops);

-- Table: rate_plan_rateplansubs

-- DROP TABLE rate_plan_rateplansubs;

CREATE TABLE rate_plan_rateplansubs
(
id serial NOT NULL,
rateplan_id character varying(32) NOT NULL,
rateplansub_id character varying(32) NOT NULL,
CONSTRAINT rate_plan_rateplansubs_pkey PRIMARY KEY (id ),
CONSTRAINT rateplan_id_refs_id_36b66e4d FOREIGN KEY (rateplan_id)
REFERENCES rate_plan (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT rateplansub_id_refs_id_aa44717e FOREIGN KEY (rateplansub_id)
REFERENCES rate_plan_sub (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT rate_plan_rateplansubs_rateplan_id_rateplansub_id_key UNIQUE (rateplan_id , rateplansub_id )
)
WITH (
OIDS=FALSE
);
ALTER TABLE rate_plan_rateplansubs
OWNER TO postgres;

-- Index: rate_plan_rateplansubs_rateplan_id

-- DROP INDEX rate_plan_rateplansubs_rateplan_id;

CREATE INDEX rate_plan_rateplansubs_rateplan_id
ON rate_plan_rateplansubs
USING btree
(rateplan_id COLLATE pg_catalog."default" );

-- Index: rate_plan_rateplansubs_rateplan_id_like

-- DROP INDEX rate_plan_rateplansubs_rateplan_id_like;

CREATE INDEX rate_plan_rateplansubs_rateplan_id_like
ON rate_plan_rateplansubs
USING btree
(rateplan_id COLLATE pg_catalog."default" varchar_pattern_ops);

-- Index: rate_plan_rateplansubs_rateplansub_id

-- DROP INDEX rate_plan_rateplansubs_rateplansub_id;

CREATE INDEX rate_plan_rateplansubs_rateplansub_id
ON rate_plan_rateplansubs
USING btree
(rateplansub_id COLLATE pg_catalog."default" );

-- Index: rate_plan_rateplansubs_rateplansub_id_like

-- DROP INDEX rate_plan_rateplansubs_rateplansub_id_like;

CREATE INDEX rate_plan_rateplansubs_rateplansub_id_like
ON rate_plan_rateplansubs
USING btree
(rateplansub_id COLLATE pg_catalog."default" varchar_pattern_ops);