Postgres Pro
Downloads
Home   >   mailing lists

Thread: significant slow down with various LIMIT

significant slow down with various LIMIT

From
norn
Date:
Hi there!

I have some mysterious slow downs with ORDER BY and LIMIT. When LIMIT
getting greater than some value (greater than 3 in my case), query
takes 4-5 secs instead of 0.25ms. All of the necessary indexes are in
place. I have no idea what to do, so any advices are welcome!

Here my queries and explain analyzes;

First Query with LIMIT 3 (fast)
-------------
explain analyze SELECT core_object.id from "core_object" INNER JOIN
"plugins_plugin_addr" ON ("core_object"."id" =
"plugins_plugin_addr"."oid_id") INNER JOIN "plugins_guide_address" ON
("plugins_plugin_addr"."address_id" = "plugins_guide_address"."id")
WHERE "plugins_guide_address"."city_id" = 4535  ORDER BY
"core_object"."id" DESC LIMIT 3;

 Limit  (cost=0.00..9.57 rows=3 width=4) (actual time=0.090..0.138
rows=3 loops=1)
   ->  Merge Join  (cost=0.00..1098182.56 rows=344125 width=4) (actual
time=0.088..0.136 rows=3 loops=1)
         Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
         ->  Nested Loop  (cost=0.00..972804.02 rows=344125 width=4)
(actual time=0.056..0.095 rows=3 loops=1)
               ->  Index Scan Backward using
plugins_plugin_addr_oid_id on plugins_plugin_addr
(cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..0.032
rows=3 loops=1)
               ->  Index Scan using plugins_guide_address_pkey on
plugins_guide_address  (cost=0.00..0.56 rows=1 width=4) (actual
time=0.017..0.018 rows=1 loops=3)
                     Index Cond: (plugins_guide_address.id =
plugins_plugin_addr.address_id)
                     Filter: (plugins_guide_address.city_id = 4535)
         ->  Index Scan using core_object_pkey_desc on core_object
(cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.026..0.028
rows=3 loops=1)
 Total runtime: 0.244 ms
(10 rows)

Second Query, the same, but with LIMIT 4 (slooooow)
-------------
explain analyze SELECT core_object.id from "core_object" INNER JOIN
"plugins_plugin_addr" ON ("core_object"."id" =
"plugins_plugin_addr"."oid_id") INNER JOIN "plugins_guide_address" ON
("plugins_plugin_addr"."address_id" = "plugins_guide_address"."id")
WHERE "plugins_guide_address"."city_id" = 4535  ORDER BY
"core_object"."id" DESC LIMIT 4;

 Limit  (cost=0.00..12.76 rows=4 width=4) (actual time=0.091..4436.795
rows=4 loops=1)
   ->  Merge Join  (cost=0.00..1098182.56 rows=344125 width=4) (actual
time=0.089..4436.791 rows=4 loops=1)
         Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
         ->  Nested Loop  (cost=0.00..972804.02 rows=344125 width=4)
(actual time=0.056..3988.249 rows=4 loops=1)
               ->  Index Scan Backward using
plugins_plugin_addr_oid_id on plugins_plugin_addr
(cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..329.942
rows=1244476 loops=1)
               ->  Index Scan using plugins_guide_address_pkey on
plugins_guide_address  (cost=0.00..0.56 rows=1 width=4) (actual
time=0.003..0.003 rows=0 loops=1244476)
                     Index Cond: (plugins_guide_address.id =
plugins_plugin_addr.address_id)
                     Filter: (plugins_guide_address.city_id = 4535)
         ->  Index Scan using core_object_pkey_desc on core_object
(cost=0.00..113516.08 rows=3091134 width=4) (actual
time=0.027..284.195 rows=1244479 loops=1)
 Total runtime: 4436.894 ms
(10 rows)

Re: significant slow down with various LIMIT

From
"Kevin Grittner"
Date:
norn <andrey.perliev@gmail.com> wrote:

> I have some mysterious slow downs with ORDER BY and LIMIT. When
> LIMIT getting greater than some value (greater than 3 in my case),
> query takes 4-5 secs instead of 0.25ms. All of the necessary
> indexes are in place. I have no idea what to do, so any advices
> are welcome!

Could you show us the output from "select version();", describe your
hardware and OS, and show us the contents of your postgresql.conf
file (with all comments removed)?  We can then give more concrete
advice than is possible with the information provided so far.

http://wiki.postgresql.org/wiki/SlowQueryQuestions

-Kevin

Re: significant slow down with various LIMIT

From
norn
Date:
Kevin, thanks for your attention!
I've read SlowQueryQuestions, but anyway can't find bottleneck...

Here requested information:
OS: Ubuntu 9.10 64bit, Postgresql 8.4.2 with Postgis
Hardware: AMD Phenom(tm) II X4 945, 8GB RAM, 2 SATA 750GB (pg db
installed in software RAID 0)
Please also note that this hardware isn't dedicated DB server, but
also serve as web server and file server.

I have about 3 million rows in core_object, 1.5 million in
plugin_plugin_addr and 1.5 million in plugins_guide_address.
When there were 300 000+ objects queries works perfectly, but as db
enlarge things go worse...

# select version();
PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu 4.4.1-4ubuntu8) 4.4.1, 64-bit
---postgresql.conf---
data_directory = '/mnt/fast/postgresql/8.4/main'
hba_file = '/etc/postgresql/8.4/main/pg_hba.conf'
ident_file = '/etc/postgresql/8.4/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/8.4-main.pid'
listen_addresses = 'localhost'
port = 5432
max_connections = 250
unix_socket_directory = '/var/run/postgresql'
ssl = true
shared_buffers = 1024MB
temp_buffers = 16MB
work_mem = 128MB
maintenance_work_mem = 512MB
fsync = off
wal_buffers = 4MB
checkpoint_segments = 16
effective_cache_size = 1536MB
log_min_duration_statement = 8000
log_line_prefix = '%t '
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
standard_conforming_strings = on
escape_string_warning = off
constraint_exclusion = on
checkpoint_completion_target = 0.9
---end postgresql.conf---

I hope this help!
Any ideas are appreciated!


On Apr 9, 12:44 am, Kevin.Gritt...@wicourts.gov ("Kevin Grittner")
wrote:
>
> Could you show us the output from "select version();", describe your
> hardware and OS, and show us the contents of your postgresql.conf
> file (with all comments removed)?  We can then give more concrete
> advice than is possible with the information provided so far.
>
> http://wiki.postgresql.org/wiki/SlowQueryQuestions
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance

Re: significant slow down with various LIMIT

From
Robert Haas
Date:
On Tue, Apr 6, 2010 at 8:42 PM, norn <andrey.perliev@gmail.com> wrote:
> I have some mysterious slow downs with ORDER BY and LIMIT. When LIMIT
> getting greater than some value (greater than 3 in my case), query
> takes 4-5 secs instead of 0.25ms. All of the necessary indexes are in
> place. I have no idea what to do, so any advices are welcome!
>
> Here my queries and explain analyzes;
>
> First Query with LIMIT 3 (fast)
> -------------
> explain analyze SELECT core_object.id from "core_object" INNER JOIN
> "plugins_plugin_addr" ON ("core_object"."id" =
> "plugins_plugin_addr"."oid_id") INNER JOIN "plugins_guide_address" ON
> ("plugins_plugin_addr"."address_id" = "plugins_guide_address"."id")
> WHERE "plugins_guide_address"."city_id" = 4535  ORDER BY
> "core_object"."id" DESC LIMIT 3;
>
>  Limit  (cost=0.00..9.57 rows=3 width=4) (actual time=0.090..0.138
> rows=3 loops=1)
>   ->  Merge Join  (cost=0.00..1098182.56 rows=344125 width=4) (actual
> time=0.088..0.136 rows=3 loops=1)
>         Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
>         ->  Nested Loop  (cost=0.00..972804.02 rows=344125 width=4)
> (actual time=0.056..0.095 rows=3 loops=1)
>               ->  Index Scan Backward using
> plugins_plugin_addr_oid_id on plugins_plugin_addr
> (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..0.032
> rows=3 loops=1)
>               ->  Index Scan using plugins_guide_address_pkey on
> plugins_guide_address  (cost=0.00..0.56 rows=1 width=4) (actual
> time=0.017..0.018 rows=1 loops=3)
>                     Index Cond: (plugins_guide_address.id =
> plugins_plugin_addr.address_id)
>                     Filter: (plugins_guide_address.city_id = 4535)
>         ->  Index Scan using core_object_pkey_desc on core_object
> (cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.026..0.028
> rows=3 loops=1)
>  Total runtime: 0.244 ms
> (10 rows)
>
> Second Query, the same, but with LIMIT 4 (slooooow)
> -------------
> explain analyze SELECT core_object.id from "core_object" INNER JOIN
> "plugins_plugin_addr" ON ("core_object"."id" =
> "plugins_plugin_addr"."oid_id") INNER JOIN "plugins_guide_address" ON
> ("plugins_plugin_addr"."address_id" = "plugins_guide_address"."id")
> WHERE "plugins_guide_address"."city_id" = 4535  ORDER BY
> "core_object"."id" DESC LIMIT 4;
>
>  Limit  (cost=0.00..12.76 rows=4 width=4) (actual time=0.091..4436.795
> rows=4 loops=1)
>   ->  Merge Join  (cost=0.00..1098182.56 rows=344125 width=4) (actual
> time=0.089..4436.791 rows=4 loops=1)
>         Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
>         ->  Nested Loop  (cost=0.00..972804.02 rows=344125 width=4)
> (actual time=0.056..3988.249 rows=4 loops=1)
>               ->  Index Scan Backward using
> plugins_plugin_addr_oid_id on plugins_plugin_addr
> (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..329.942
> rows=1244476 loops=1)
>               ->  Index Scan using plugins_guide_address_pkey on
> plugins_guide_address  (cost=0.00..0.56 rows=1 width=4) (actual
> time=0.003..0.003 rows=0 loops=1244476)
>                     Index Cond: (plugins_guide_address.id =
> plugins_plugin_addr.address_id)
>                     Filter: (plugins_guide_address.city_id = 4535)
>         ->  Index Scan using core_object_pkey_desc on core_object
> (cost=0.00..113516.08 rows=3091134 width=4) (actual
> time=0.027..284.195 rows=1244479 loops=1)
>  Total runtime: 4436.894 ms
> (10 rows)

What do you get with no LIMIT at all?

...Robert

Re: significant slow down with various LIMIT

From
Helio Campos Mello de Andrade
Date:
1 ) Limit  (cost=0.00..9.57 rows=3 width=4) (actual time=0.090..0.138 rows=3 loops=1)
2 ) Limit  (cost=0.00..12.76 rows=4 width=4) (actual time=0.091..4436.795 rows=4 loops=1)
1 )     ->  Merge Join  (cost=0.00..1098182.56 rows=344125 width=4) (actual time=0.088..0.136 rows=3 loops=1)
2 )     ->  Merge Join  (cost=0.00..1098182.56 rows=344125 width=4) (actual time=0.089..4436.791 rows=4 loops=1)
1 )             Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
2 )             Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
 1 )                 ->  Nested Loop  (cost=0.00..972804.02 rows=344125 width=4) (actual time=0.056..0.095 rows=3 loops=1)
2 )                 ->  Nested Loop  (cost=0.00..972804.02 rows=344125 width=4) (actual time=0.056..3988.249 rows=4 loops=1)
###################################################################################################################################################################################################
1 )                     ->  Index Scan Backward using plugins_plugin_addr_oid_id on plugins_plugin_addr (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..0.032 rows=3 loops=1)
2 )                     ->  Index Scan Backward using plugins_plugin_addr_oid_id on plugins_plugin_addr (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..329.942 rows=1244476 loops=1)

1 )                     ->  Index Scan using plugins_guide_address_pkey on plugins_guide_address  (cost=0.00..0.56 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=3)
2 )                     ->  Index Scan using plugins_guide_address_pkey on plugins_guide_address  (cost=0.00..0.56 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1244476)
###################################################################################################################################################################################################
 - I am not an expert in the matter but in the first query it took only 3 loops to find 1 row and in the second it looped 1244476 times to find no row at all. Is it possible that there is no other row in the table that match the data you are trying to retrieve?
 - Have you tried to recreate the index of the table? It could be that its damaged in some way that postgres can not use the index and its making a full search in the table. Again, it's just a wild guess.

1 )                             Index Cond: (plugins_guide_address.id = plugins_plugin_addr.address_id)
2 )                             Index Cond: (plugins_guide_address.id = plugins_plugin_addr.address_id)
 1 )                             Filter: (plugins_guide_address.city_id = 4535)
2 )                             Filter: (plugins_guide_address.city_id = 4535)
 1 )             ->  Index Scan using core_object_pkey_desc on core_object (cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.026..0.028 rows=3 loops=1)
2 )             ->  Index Scan using core_object_pkey_desc on core_object (cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.027..284.195 rows=1244479 loops=1)
 1 ) Total runtime: 0.244 ms
2 ) Total runtime: 4436.894 ms

Regards...

--
Helio Campos Mello de Andrade

Re: significant slow down with various LIMIT

From
norn
Date:
On Apr 10, 6:48 am, robertmh...@gmail.com (Robert Haas) wrote:
> On Tue, Apr 6, 2010 at 8:42 PM, norn <andrey.perl...@gmail.com> wrote:
> > I have some mysterious slow downs with ORDER BY and LIMIT. When LIMIT
> > getting greater than some value (greater than 3 in my case), query
> > takes 4-5 secs instead of 0.25ms. All of the necessary indexes are in
> > place. I have no idea what to do, so any advices are welcome!
>
> > Here my queries and explain analyzes;
>
> > First Query with LIMIT 3 (fast)
> > -------------
> > explain analyze SELECT core_object.id from "core_object" INNER JOIN
> > "plugins_plugin_addr" ON ("core_object"."id" =
> > "plugins_plugin_addr"."oid_id") INNER JOIN "plugins_guide_address" ON
> > ("plugins_plugin_addr"."address_id" = "plugins_guide_address"."id")
> > WHERE "plugins_guide_address"."city_id" = 4535  ORDER BY
> > "core_object"."id" DESC LIMIT 3;
>
> >  Limit  (cost=0.00..9.57 rows=3 width=4) (actual time=0.090..0.138
> > rows=3 loops=1)
> >   ->  Merge Join  (cost=0.00..1098182.56 rows=344125 width=4) (actual
> > time=0.088..0.136 rows=3 loops=1)
> >         Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
> >         ->  Nested Loop  (cost=0.00..972804.02 rows=344125 width=4)
> > (actual time=0.056..0.095 rows=3 loops=1)
> >               ->  Index Scan Backward using
> > plugins_plugin_addr_oid_id on plugins_plugin_addr
> > (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..0.032
> > rows=3 loops=1)
> >               ->  Index Scan using plugins_guide_address_pkey on
> > plugins_guide_address  (cost=0.00..0.56 rows=1 width=4) (actual
> > time=0.017..0.018 rows=1 loops=3)
> >                     Index Cond: (plugins_guide_address.id =
> > plugins_plugin_addr.address_id)
> >                     Filter: (plugins_guide_address.city_id = 4535)
> >         ->  Index Scan using core_object_pkey_desc on core_object
> > (cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.026..0.028
> > rows=3 loops=1)
> >  Total runtime: 0.244 ms
> > (10 rows)
>
> > Second Query, the same, but with LIMIT 4 (slooooow)
> > -------------
> > explain analyze SELECT core_object.id from "core_object" INNER JOIN
> > "plugins_plugin_addr" ON ("core_object"."id" =
> > "plugins_plugin_addr"."oid_id") INNER JOIN "plugins_guide_address" ON
> > ("plugins_plugin_addr"."address_id" = "plugins_guide_address"."id")
> > WHERE "plugins_guide_address"."city_id" = 4535  ORDER BY
> > "core_object"."id" DESC LIMIT 4;
>
> >  Limit  (cost=0.00..12.76 rows=4 width=4) (actual time=0.091..4436.795
> > rows=4 loops=1)
> >   ->  Merge Join  (cost=0.00..1098182.56 rows=344125 width=4) (actual
> > time=0.089..4436.791 rows=4 loops=1)
> >         Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
> >         ->  Nested Loop  (cost=0.00..972804.02 rows=344125 width=4)
> > (actual time=0.056..3988.249 rows=4 loops=1)
> >               ->  Index Scan Backward using
> > plugins_plugin_addr_oid_id on plugins_plugin_addr
> > (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..329.942
> > rows=1244476 loops=1)
> >               ->  Index Scan using plugins_guide_address_pkey on
> > plugins_guide_address  (cost=0.00..0.56 rows=1 width=4) (actual
> > time=0.003..0.003 rows=0 loops=1244476)
> >                     Index Cond: (plugins_guide_address.id =
> > plugins_plugin_addr.address_id)
> >                     Filter: (plugins_guide_address.city_id = 4535)
> >         ->  Index Scan using core_object_pkey_desc on core_object
> > (cost=0.00..113516.08 rows=3091134 width=4) (actual
> > time=0.027..284.195 rows=1244479 loops=1)
> >  Total runtime: 4436.894 ms
> > (10 rows)
>
> What do you get with no LIMIT at all?
>
> ...Robert
>
> --
> Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance

Without using limit query takes 5-6 seconds, but I have to get only a
couple of last rows with a cost of 200-300ms