Re: bad plan and LIMIT

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




I had tried using exists but both the forms of the query (with limit and without) performed much worse.

   James

On May 1, 2009, at 4:22 AM, Adam Ruth wrote:

You could try changing the IN to an EXISTS, that may alter how the optimizer weighs the limit.


SELECT ID FROM ps_image WHERE EXISTS (SELECT null FROM ps_gallery_image WHERE gallery_id ='G00007ejKGoWS_cY' and image_id = ps_image.id) ORDER BY LOWER(FILE_NAME) ASC

On 30/04/2009, at 3:51 AM, James Nelson wrote:


Hi, I'm hoping you guys can help with improving this query I'm having a problem with. The main problem is that the query plan changes depending on the value of the LIMIT clause, with small values using a poor plan and running very slowly. The two times are roughly 5 minutes for the bad plan and 1.5 secs for the good plan.

I have read a little about how the query planner takes into account the limit clause, and I can see the effect this has on the costs shown by explain. The problem is that the estimated cost ends up being wildly inaccurate. I'm not sure if this a problem with the planner or if it is something I am doing wrong on my end.

the query (without the limit clause):

SELECT ID FROM ps_image WHERE id IN (SELECT image_id FROM ps_gallery_image WHERE gallery_id='G00007ejKGoWS_cY') ORDER BY LOWER(FILE_NAME) ASC

The ps_image table has about 24 million rows, ps_gallery_image has about 14 million. The query above produces roughly 50 thousand rows.

When looking at the explain with the limit, I can see the interpolation that the planner does for the limit node (arriving at a final cost of 458.32 for this example) but not sure why it is inaccurate compared to the actual times.

Thanks in advance for taking a look at this, let me know if there is additional information I should provide.

Some information about the tables  and the explains follow below.

James Nelson

[james@db2 ~] psql --version
psql (PostgreSQL) 8.3.5
contains support for command-line editing

photoshelter=# \d ps_image
                            Table "public.ps_image"
 Column     |           Type           |                 Modifiers
---------------+-------------------------- +-------------------------------------------
id            | character varying(16)    | not null
user_id       | character varying(16)    |
album_id      | character varying(16)    | not null
parent_id     | character varying(16)    |
file_name     | character varying(200)   |
file_size     | bigint                   |
.... 20 rows snipped ....
Indexes:
 "ps_image_pkey" PRIMARY KEY, btree (id)
 "i_file_name_l" btree (lower(file_name::text))
.... indexes, fk constraints and triggers snipped ....

photoshelter=# \d ps_gallery_image
               Table "public.ps_gallery_image"
 Column     |           Type           |       Modifiers
---------------+--------------------------+------------------------
gallery_id    | character varying(16)    | not null
image_id      | character varying(16)    | not null
display_order | integer                  | not null default 0
caption       | character varying(2000)  |
ctime         | timestamp with time zone | not null default now()
mtime         | timestamp with time zone | not null default now()
id            | character varying(16)    | not null
Indexes:
 "ps_gallery_image_pkey" PRIMARY KEY, btree (id)
 "gi_gallery_id" btree (gallery_id)
 "gi_image_id" btree (image_id)
Foreign-key constraints:
"ps_gallery_image_gallery_id_fkey" FOREIGN KEY (gallery_id) REFERENCES ps_gallery(id) ON DELETE CASCADE "ps_gallery_image_image_id_fkey" FOREIGN KEY (image_id) REFERENCES ps_image(id) ON DELETE CASCADE
Triggers:
ps_image_gi_sync AFTER INSERT OR DELETE OR UPDATE ON ps_gallery_image FOR EACH ROW EXECUTE PROCEDURE ps_image_sync()

= = = = = = = = = = = = = = = = =====================================================================
explain analyze for bad plan

photoshelter=# explain analyze SELECT ID FROM ps_image WHERE id IN (SELECT image_id FROM ps_gallery_image WHERE gallery_id='G00007ejKGoWS_cY') ORDER BY LOWER(FILE_NAME) ASC limit 1; QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..458.32 rows=1 width=36) (actual time=709831.847..709831.847 rows=1 loops=1) -> Nested Loop IN Join (cost=0.00..17700128.78 rows=38620 width=36) (actual time=709831.845..709831.845 rows=1 loops=1) -> Index Scan using i_file_name_l on ps_image (cost=0.00..1023863.22 rows=24460418 width=36) (actual time=0.063..271167.293 rows=8876340 loops=1) -> Index Scan using gi_image_id on ps_gallery_image (cost=0.00..0.85 rows=1 width=17) (actual time=0.048..0.048 rows=0 loops=8876340) Index Cond: ((ps_gallery_image.image_id)::text = (ps_image.id)::text) Filter: ((ps_gallery_image.gallery_id)::text = 'G00007ejKGoWS_cY'::text)
Total runtime: 709831.932 ms

= = = = = = = = = = = = = = = = =====================================================================
explain analyze for good plan

photoshelter=# explain analyze SELECT ID FROM ps_image WHERE id IN (SELECT image_id FROM ps_gallery_image WHERE gallery_id='G00007ejKGoWS_cY') ORDER BY LOWER(FILE_NAME) ASC limit 600; QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=154650.99..154652.49 rows=600 width=36) (actual time=1886.038..1886.404 rows=600 loops=1) -> Sort (cost=154650.99..154747.54 rows=38619 width=36) (actual time=1886.038..1886.174 rows=600 loops=1)
      Sort Key: (lower((ps_image.file_name)::text))
      Sort Method:  top-N heapsort  Memory: 75kB
-> Nested Loop (cost=42394.02..152675.86 rows=38619 width=36) (actual time=135.132..1838.491 rows=50237 loops=1) -> HashAggregate (cost=42394.02..42780.21 rows=38619 width=17) (actual time=135.079..172.563 rows=50237 loops=1) -> Index Scan using gi_gallery_id on ps_gallery_image (cost=0.00..42271.79 rows=48891 width=17) (actual time=0.063..97.539 rows=50237 loops=1) Index Cond: ((gallery_id)::text = 'G00007ejKGoWS_cY'::text) -> Index Scan using ps_image_pkey on ps_image (cost=0.00..2.83 rows=1 width=36) (actual time=0.031..0.031 rows=1 loops=50237) Index Cond: ((ps_image.id)::text = (ps_gallery_image.image_id)::text)
Total runtime: 1886.950 ms







--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx )
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux