Re: How to get the total number of rows returned by query when using a cursor? - Mailing list pgsql-general

> Sounds like the planner is misestimating costs and choosing the
> wrong plan.
> Please show examples (EXPLAIN ANALYZE for same query with and without
> LIMIT would be useful).

Here it goes. This query is 6 times slower with LIMIT 10 added (I had slower
ones, but those are complex). I must say I rewrote it using subselects, thus
speeding it up in general and getting rid of the problem, but this is the
form I used on MySQL and it worked fast.

The purpose is to find users that list users 'asta' and 'furrr' as their
friends and are themselves listed as friends by the user 'chalky'.

SELECT
   users.*
  FROM
   users, friends AS f0, friends AS f1, friends AS fo0
  WHERE
   f0.nick = users.nick AND
   f1.nick = f0.nick AND
   fo0.friend_nick = f1.nick AND
   f0.friend_nick = 'asta' AND
   f1.friend_nick = 'furrr' AND
   fo0.nick = 'chalky'
;

Without LIMIT:
---------------------
 Merge Join  (cost=43404.20..45067.61 rows=1460751 width=660) (actual
time=4617.53..4633.82 rows=5 loops=1)
   Merge Cond: ("outer".nick = "inner".nick)
   ->  Merge Join  (cost=26432.36..27412.27 rows=75288 width=38) (actual
time=125.43..136.41 rows=17 loops=1)
         Merge Cond: ("outer".nick = "inner".friend_nick)
         ->  Sort  (cost=13216.18..13225.88 rows=3880 width=19) (actual
time=115.29..115.98 rows=388 loops=1)
               Sort Key: f0.nick
               ->  Index Scan using friend_nick__nick on friends f0
(cost=0.00..12984.87 rows=3880 width=19) (actual time=1.31..102.35 rows=391
loops=1)
                     Index Cond: (friend_nick = 'asta'::character varying)
         ->  Sort  (cost=13216.18..13225.88 rows=3880 width=19) (actual
time=9.76..9.94 rows=87 loops=1)
               Sort Key: fo0.friend_nick
               ->  Index Scan using nick__friend_nick on friends fo0
(cost=0.00..12984.87 rows=3880 width=19) (actual time=1.42..7.18 rows=87
loops=1)
                     Index Cond: (nick = 'chalky'::character varying)
   ->  Materialize  (cost=17089.36..17089.36 rows=3880 width=622) (actual
time=4491.16..4491.82 rows=337 loops=1)
         ->  Merge Join  (cost=16971.84..17089.36 rows=3880 width=622)
(actual time=4037.07..4485.64 rows=360 loops=1)
               Merge Cond: ("outer".nick = "inner".nick)
               ->  Sort  (cost=13216.18..13225.88 rows=3880 width=19)
(actual time=133.72..135.02 rows=448 loops=1)
                     Sort Key: f1.nick
                     ->  Index Scan using friend_nick__nick on friends f1
(cost=0.00..12984.87 rows=3880 width=19) (actual time=1.30..118.64 rows=448
loops=1)
                           Index Cond: (friend_nick = 'furrr'::character
varying)
               ->  Sort  (cost=3755.66..3780.47 rows=9923 width=603) (actual
time=3902.67..4109.18 rows=9921 loops=1)
                     Sort Key: users.nick
                     ->  Seq Scan on users  (cost=0.00..1134.23 rows=9923
width=603) (actual time=14.61..1839.57 rows=9923 loops=1)
Total runtime: 4643.07 msec
(23 rows)

---------------------

With LIMIT 10:

 Limit  (cost=0.00..24.79 rows=10 width=660) (actual time=8380.84..23224.93
rows=5 loops=1)
   ->  Merge Join  (cost=0.00..3620915.28 rows=1460751 width=660) (actual
time=8380.82..23224.88 rows=5 loops=1)
         Merge Cond: ("outer".nick = "inner".nick)
         ->  Nested Loop  (cost=0.00..2820642.36 rows=75288 width=38)
(actual time=234.64..15567.35 rows=17 loops=1)
               ->  Index Scan using nick__friend_nick on friends f0
(cost=0.00..2509966.71 rows=3880 width=19) (actual time=5.92..15360.73
rows=391 loops=1)
                     Filter: (friend_nick = 'asta'::character varying)
               ->  Index Scan using nick__friend_nick on friends fo0
(cost=0.00..79.82 rows=19 width=19) (actual time=0.48..0.48 rows=0
loops=391)
                     Index Cond: ((fo0.nick = 'chalky'::character varying)
AND (fo0.friend_nick = "outer".nick))
         ->  Materialize  (cost=799561.12..799561.12 rows=3880 width=622)
(actual time=7650.62..7651.35 rows=337 loops=1)
               ->  Nested Loop  (cost=0.00..799561.12 rows=3880 width=622)
(actual time=10.42..7640.60 rows=360 loops=1)
                     ->  Index Scan using journals_0_pkey on users
(cost=0.00..5102.15 rows=9923 width=603) (actual time=1.03..2548.42
rows=9923 loops=1)
                     ->  Index Scan using nick__friend_nick on friends f1
(cost=0.00..79.82 rows=19 width=19) (actual time=0.47..0.47rows=0
loops=9923)
                           Index Cond: ((f1.nick = "outer".nick) AND
(f1.friend_nick = 'furrr'::character varying))
Total runtime: 23227.58 msec
(14 rows)

-------------------

Relevant table descriptions:

                         Table "public.users"
      Column       |            Type             |     Modifiers
-------------------+-----------------------------+--------------------
 id                | integer                     |
 nick              | character varying(15)       | not null
 name              | character varying           |
 website           | character varying(1024)     |
 website_name      | character varying           |
 country           | character varying           |
 city              | character varying           |
 birth_date        | date                        |
 email             | character varying(50)       |
 icq               | bigint                      |
 bio               | text                        |
 account_type      | character varying           |
 is_community      | boolean                     | not null
 friends_num       | integer                     | not null default 0
 friend_of_num     | integer                     | not null default 0
 member_of_num     | integer                     | not null default 0
 memories_num      | integer                     | not null default 0
 time_created      | timestamp without time zone | not null
 time_updated      | timestamp without time zone |
 journal_entries   | integer                     | not null default 0
 comments_received | integer                     | not null default 0
 comments_posted   | integer                     | not null default 0
 picture           | character varying           |
 interests_num     | integer                     | not null default 0
 memored_num       | integer                     | not null default 0
 age               | smallint                    |
 rank_pos          | integer                     |
 rank              | numeric(10,3)               |
 location          | character varying(255)      |
Indexes: journals_0_pkey primary key btree (nick),
         age btree (age),
         country btree (country),
         is_community btree (is_community),
         location_info btree (country, city),
         picture btree (picture),
         rank btree (rank),
         time_updated btree (time_updated)



             Table "public.friends"
   Column    |         Type          | Modifiers
-------------+-----------------------+-----------
 nick        | character varying(15) | not null
 friend_nick | character varying(15) | not null
Indexes: friends2_pkey primary key btree (nick, friend_nick),
         friend_nick__nick unique btree (friend_nick, nick),
         nick__friend_nick unique btree (nick, friend_nick)



> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, September 23, 2003 19:43
> To: Egor Shipovalov
> Cc: Pgsql-General
> Subject: Re: [GENERAL] How to get the total number of rows returned by
> query when using a cursor?
>
>
> "Egor Shipovalov" <pgsql_list@eonline.ru> writes:
> > The interesting thing is that some queries are tens (!) of
> times slower with
> > LIMIT that without. These contain a number of joins on
> subselects. If you're
> > interested, I'll post examples.
>
>
>             regards, tom lane