473,542 Members | 16,235 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to get the total number of rows returned by query when using a cursor?

I'm implementing paging through search results using cursors. Is there a
better way to know total number of rows under a cursor than running a
separate COUNT(*) query? I think PostgreSQL is bound to know this number
after the first FETCH, isn't it?

On a side note, why queries using LIMIT are SO terribly slow, compared to
cursors and sometimes even ones without LIMIT? Shouldn't LIMIT be internally
implemented using cursor mechanism then?

Best regards,
Egor Shipovalov.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #1
7 12038
Egor Shipovalov wrote:
I'm implementing paging through search results using cursors. Is there a
better way to know total number of rows under a cursor than running a
separate COUNT(*) query? I think PostgreSQL is bound to know this number
after the first FETCH, isn't it?


Using libpq, yes. You can use PQntuples. See
http://developer.postgresql.org/docs...EC-SELECT-INFO

Unfortunately this will retrieve the entire resultselt before returning the
control. When libpq will have chuncking of data, this will be much more
flexible, as you are expecting it now, I guess.

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #2
Thanks. In fact, I've thought about that too, but it doesn't seem to work.
I'm using Perl Pg module which, I'm guessing, is just a wrapper around
libpq. This is the code I used to test it:

---------------
use Pg;

$conn = Pg::connectdb('user=my_user dbname=my_db');
$conn->exec('BEGIN');
$r = $conn->exec("DECLARE my_cursor CURSOR FOR SELECT nick FROM users ORDER
BY nick DESC");

print $r->ntuples; # prints 0

$r = $conn->exec('FETCH 10 FROM my_cursor');

print $r->ntuples; # prints 10
--------------

Too bad we seem to be stuck with COUNT(*) for now. Are there any
fundamental problems getting to that cursor count data?
Judging from the lack of absolute cursor positioning it appears somewhat
like so ;(

Best regards,
Egor Shipovalov.

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org]On Behalf Of Shridhar
Daithankar
Sent: Tuesday, September 23, 2003 18:59
To: Pgsql-General
Subject: Re: [GENERAL] How to get the total number of rows
returned by query
Egor Shipovalov wrote:
I'm implementing paging through search results using cursors. Is there a
better way to know total number of rows under a cursor than running a
separate COUNT(*) query? I think PostgreSQL is bound to know this number
after the first FETCH, isn't it?


Using libpq, yes. You can use PQntuples. See
http://developer.postgresql.org/docs...exec.html#LIBP
Q-EXEC-SELECT-INFO

Unfortunately this will retrieve the entire resultselt before
returning the
control. When libpq will have chuncking of data, this will be much more
flexible, as you are expecting it now, I guess.

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #3
"Egor Shipovalov" <pg********@eonline.ru> writes:
I'm implementing paging through search results using cursors. Is there a
better way to know total number of rows under a cursor than running a
separate COUNT(*) query? I think PostgreSQL is bound to know this number
after the first FETCH, isn't it?
Why would you think that? In general Postgres doesn't know the number
of rows until it reaches the end of the query.
On a side note, why queries using LIMIT are SO terribly slow, compared to
cursors and sometimes even ones without LIMIT?


Generally they're not particularly slow. Perhaps you should show us the
EXPLAIN ANALYZE results for your problem case.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #4
> > I'm implementing paging through search results using cursors. Is there a
better way to know total number of rows under a cursor than running a
separate COUNT(*) query? I think PostgreSQL is bound to know this number
after the first FETCH, isn't it?


Why would you think that? In general Postgres doesn't know the number
of rows until it reaches the end of the query.


Sorry, I thought that because my query was sorted. For unsorted queries,
yes, there's usually no way to know until you reach the end.
On a side note, why queries using LIMIT are SO terribly slow,

compared to
cursors and sometimes even ones without LIMIT?


Generally they're not particularly slow. Perhaps you should show us the
EXPLAIN ANALYZE results for your problem case.


I did just that, but the plan was too complicated for me to comprehend ;)
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.

Best regards,
Egor Shipovalov.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #5
"Egor Shipovalov" <pg********@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.


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).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #6
> 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:tg*@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" <pg********@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #7
Egor Shipovalov wrote:
Limit (cost=0.00..24.79 rows=10 width=660) (actual time=8380.84..23224.93
rows=5 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)
AFAICS this estimated rows=3880 vs. actual rows=391 misestimation is the
source of your problem. If you can convince the planner that
friend_nick='asta' gives only 400 rows, it probably will switch to an
index scan using friend_nick__nick with an estimated cost of ~ 1600 and
an actual time of ~ 100.
-> 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))
With better statistics this might change to a much cheaper
-> Nested Loop
-> Index Scan using friend_nick__nick on f1 (cost=..1600 rows=400)
-> Index Scan using u_pkey on users ( ... loops=448)

.... unless the planner finds an ever faster plan.

So try
ALTER TABLE friends
ALTER COLUMN friend_nick SET STATISTICS 100;
ANALYSE friends;

and let us know how this affects your query.

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)


BTW, this last index is useless because it duplicates the primary key.

Servus
Manfred
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
7777
by: krystoffff | last post by:
Hi I would like to paginate the results of a query on several pages. So I use a query with a limit X offset Y to display X results on a page, ok. But for the first page, I need to run the same query with a count(*) to know how many pages I will get (number total of rows/ X). The problem is my query is very slow (maybe 5s) because there...
0
5758
by: D. Dante Lorenso | last post by:
I need to know that original number of rows that WOULD have been returned by a SELECT statement if the LIMIT / OFFSET where not present in the statement. Is there a way to get this data from PG ? SELECT ... ; ----> returns 100,000 rows
19
81762
by: xixi | last post by:
i want to use sql query to open a resultset, say i want the cursor point to whatever position i start with by giving a row number , so is there anyway i can use the sql function to do that, so when the resultset return, the first row will be the absolute row dertermine by the row number
1
13414
by: BF | last post by:
Hello, I am trying to find out if there is a way to limit the number of rows returned when a cursor is opened. I am using DB2 version 7 on z/OS. The SELECT statement in my DECLARE CURSOR statement could potentially return 400,000+ rows, but I really only want the first 15 records found. I can put a lower-bound (col >= "xxx") in the WHERE...
4
14381
by: Chris Tremblay | last post by:
I am trying to figure out how to go about retrieving the number of results returned from my queries in SQL server from VB.NET without using a the Select Count(*) query. The method that I was using was the following: Take the query that I am executing, copy the query and turn it into a count query, run the count query, then execute the...
1
1983
by: Phil Endecott | last post by:
Dear Postgresql experts, According to the documentation for MOVE, it returns the number of rows that it has moved over. It seems to me that this is true for MOVE FORWARD n, but not for MOVE RELATIVE n or MOVE ABSOLUTE n when it always returns 1: db=> declare c scroll cursor for select * from p; DECLARE CURSOR db=> move absolute -1 in c;
5
12242
by: alingsjtu | last post by:
Hello, every body. When execute dynamic generated multiple OPENQUERY statements (which linkes to DB2) in SQLServer, I always got SQL1040N The maximum number of applications is already connected to the database. SQLSTATE=57030. Background: I created a linked server to DB2 8.1 database which called GRR_DB2Server. In my stored procedure...
8
18542
by: trint | last post by:
I have a select statement that all I want to do is get the number of rows returned in my query: string strSQLAccountInfo1 = "select * " + "FROM orders " + "where user_id = '" + Class1.loginUserid.Trim() + "' "; SqlCommand cmdAL = new SqlCommand(strSQLAccountInfo1, cnSQLAccountInfo1);
2
10609
by: kveerareddy | last post by:
How can i get the total rows of a table: If i use "select count(*) from <tablename>" it will take lot of time to get the total row count. for example in MSSQL we have "SELECT ROWS FROM sysindexes WHERE id = OBJECT_ID(<Table name>) AND indid < 2" I am looking some system index tables which hold the total rows as a statistic values of the...
0
7321
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7564
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7718
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7662
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5861
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3377
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3372
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1788
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
941
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.