Re: [pgsql-zh-general] 关于相同的语句limit不同导致不同的性能

Jov
blog: http:amutu.com/blog <http://amutu.com/blog>

2014-07-18 13:16 GMT+08:00 duanhongyi <duanhongyi(at)mxnavi(dot)com>:

> 大家好：
>
> 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)"
>
> rows=402233跟下面的查询计划相差较大，在运行第一个sql前，你是否对表做过analyze？

> " 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);
>
>
>
>