Обсуждение: slow query execution

Hi all,

This query executes very slow:

select (select count(*) from customer where id <= a.id) as row, id, from customer as a order by id;

Where customer has id column and others and has also index on id column. The table has about 10.000+ records. When used
with"explain", it gives me this output:
 
                                       QUERY PLAN

-----------------------------------------------------------------------------------------Sort
(cost=6513774.23..6513801.37rows=10855 width=14)  Sort Key: id  ->  Seq Scan on customer a  (cost=0.00..6513046.62
rows=10855width=14)        SubPlan          ->  Aggregate  (cost=599.94..599.95 rows=1 width=0)                ->
BitmapHeap Scan on customer  (cost=28.66..590.89 rows=361 width=0)                      Recheck Cond: (id <= $0)
             ->  Bitmap Index Scan on iid  (cost=0.00..28.66 rows=361 width=0)                            Index Cond:
(id<= $0)
 
(9 riadkov)


So is there any solution to speed up this query? Or can I get the same result but with some other query?

thanks

Trigve




      
____________________________________________________________________________________
Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games.
http://sims.yahoo.com/  

On Wed, May 30, 2007 at 08:56:45AM -0700, Trigve Siver wrote:
> Hi all,
> 
> This query executes very slow:
> 
> select (select count(*) from customer where id <= a.id) as row, id,
> from customer as a order by id;

So you are trying to get the ordinal position of every ID in the
table?  I'm not surprised it takes a long time -- you have to join
the whole table to itself and then do a lot of counting.  Are you
just trying to get the "row number" for your query answer?  You can
do this with a temporary sequence, among other approaches, more
cheaply.

A


-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard 
to use, but at least there is a lot of code underneath."    --Damien Katz

Hi, 
Thanks for reply, As you have mentioned I need to get row numbers for my query, so when I make some other query with
samedata I will know which row number has a particular ID. As you mentioned "You can
 
do this with a temporary sequence, among other approaches...". Can you point me to some sources or give me some
examples,please?
 

Thanks 

Trigve

----- Original Message ----
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Sent: Wednesday, May 30, 2007 6:45:53 PM
Subject: Re: [SQL] slow query execution

On Wed, May 30, 2007 at 08:56:45AM -0700, Trigve Siver wrote:
> Hi all,
> 
> This query executes very slow:
> 
> select (select count(*) from customer where id <= a.id) as row, id,
> from customer as a order by id;

So you are trying to get the ordinal position of every ID in the
table?  I'm not surprised it takes a long time -- you have to join
the whole table to itself and then do a lot of counting.  Are you
just trying to get the "row number" for your query answer?  You can
do this with a temporary sequence, among other approaches, more
cheaply.

A


-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard 
to use, but at least there is a lot of code underneath."       --Damien Katz

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faq




      
____________________________________________________________________________________Yahoo! oneSearch: Finally, mobile
search
 
that gives answers, not web links. 
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

On 5/30/07, Trigve Siver <trigves@yahoo.com> wrote:
> Can you point me to some sources
> or give me some examples, please?

CREATE OR REPLACE FUNCTION ROWNUM() RETURNS BIGINT AS
$$BEGIN    RETURN NEXTVAL('ROWNUM_SEQ');EXCEPTION WHEN OTHERS THEN    CREATE TEMP SEQUENCE ROWNUM_SEQ;    RETURN
NEXTVAL('ROWNUM_SEQ');END;
$$
LANGUAGE 'PLPGSQL';

SELECT ROWNUM(), S.X
FROM GENERATE_SERIES(5,1,-1) S(X);

Remember to reset the sequence value if you use this more than once in
the same session.

Thanks a lot

Trigve

----- Original Message ----
From: Rodrigo De León <rdeleonp@gmail.com>
To: pgsql-sql@postgresql.org; Trigve Siver <trigves@yahoo.com>
Sent: Wednesday, May 30, 2007 7:28:47 PM
Subject: Re: [SQL] slow query execution

On 5/30/07, Trigve Siver <trigves@yahoo.com> wrote:
> Can you point me to some sources
> or give me some examples, please?

CREATE OR REPLACE FUNCTION ROWNUM() RETURNS BIGINT AS
$$   BEGIN       RETURN NEXTVAL('ROWNUM_SEQ');   EXCEPTION WHEN OTHERS THEN       CREATE TEMP SEQUENCE ROWNUM_SEQ;
RETURN NEXTVAL('ROWNUM_SEQ');   END; 
$$
LANGUAGE 'PLPGSQL';

SELECT ROWNUM(), S.X
FROM GENERATE_SERIES(5,1,-1) S(X);

Remember to reset the sequence value if you use this more than once in
the same session.





____________________________________________________________________________________Take the Internet to Go: Yahoo!Go
putsthe Internet in your pocket: mail, news, photos & more.  
http://mobile.yahoo.com/go?refer=1GNXIC

On Wed, May 30, 2007 at 10:03:16AM -0700, Trigve Siver wrote:
> Hi, Thanks for reply, As you have mentioned I need to get row
> numbers for my query, so when I make some other query with same
> data I will know which row number has a particular ID. 

Oh, wait.  If _that's_ your plan, then this will never work.  The
data could change, and your row numbers would come out wrong. 
What do you need "row numbers" for anyway?  The very idea is inimical
to SQL, because the data is fundamentally unordered.  

> As you
> mentioned "You can do this with a temporary sequence, among other
> approaches...". Can you point me to some sources or give me some
> examples, please?

BEGIN;
CREATE SEQUENCE temp_seq;
SELECT nextval('temp_seq'), other stuff from table;
DROP SEQUENCE temp_seq;
COMMIT/ROLLBACK;

If you only select, you don't have to do the DROP, you just ROLLBACK.

I think there's some nifty way to get generate_series to do this too,
but I don't know it offhand (generating row numbers sounds to me like
a bad idea, so I don't do it).

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca

>----- Original Message ----
>From: Andrew Sullivan <ajs@crankycanuck.ca>
>To: pgsql-sql@postgresql.org
>Sent: Wednesday, May 30, 2007 7:39:18 PM
>Subject: Re: [SQL] slow query execution
>
>On Wed, May 30, 2007 at 10:03:16AM -0700, Trigve Siver wrote:
>> Hi, Thanks for reply, As you have mentioned I need to get row
>> numbers for my query, so when I make some other query with same
>> data I will know which row number has a particular ID. 
>
>Oh, wait.  If _that's_ your plan, then this will never work.  The
>data could change, and your row numbers would come out wrong. 
>What do you need "row numbers" for anyway?  The very idea is inimical
>to SQL, because the data is fundamentally unordered.  

I want to do it only for some queries. So when I need query like that "SELECT * from t1"
I need to add row_numbers there. So I will have "query with row_numbers" (where row_numbers 
are row numbers of my virtual list view). Then when I perform search I also get row_numbers for IDs of search result
andI can highlight items in my list view. I haven't found other solution that met my requirements.
 

I want also do it with cursors.

[...]

>I think there's some nifty way to get generate_series to do this too,
>but I don't know it offhand (generating row numbers sounds to me like
>a bad idea, so I don't do it).

I have tried with generate_series but without success.

thanks

Trigve




      
____________________________________________________________________________________Luggage? GPS? Comic books? 
Check out fitting gifts for grads at Yahoo! Search
http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz

On Wed, May 30, 2007 at 11:08:02AM -0700, Trigve Siver wrote:

> I want to do it only for some queries. So when I need query like
> that "SELECT * from t1" I need to add row_numbers there. So I will
> have "query with row_numbers" (where row_numbers are row numbers of
> my virtual list view). Then when I perform search I also get
> row_numbers for IDs of search result and I can highlight items in
> my list view. I haven't found other solution that met my
> requirements.

You really need to ensure you keep that ORDER BY in there, then.  The
order that SQL returns in is not determined until the data has come
back.  Are you doing this all in one serialisable transaction,
though?  If not, what guarantee will you have that new data won't
mess up your row numbering from query to query?

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The plural of anecdote is not data.    --Roger Brinner

>----- Original Message ----
>From: Andrew Sullivan <ajs@crankycanuck.ca>
>To: pgsql-sql@postgresql.org
>Sent: Wednesday, May 30, 2007 8:30:32 PM
>Subject: Re: [SQL] slow query execution
>
>On Wed, May 30, 2007 at 11:08:02AM -0700, Trigve Siver wrote:
>
>> I want to do it only for some queries. So when I need query like
>> that "SELECT * from t1" I need to add row_numbers there. So I will
>> have "query with row_numbers" (where row_numbers are row numbers of
>> my virtual list view). Then when I perform search I also get
>> row_numbers for IDs of search result and I can highlight items in
>> my list view. I haven't found other solution that met my
>> requirements.
>
>You really need to ensure you keep that ORDER BY in there, then.  The
>order that SQL returns in is not determined until the data has come
>back.  Are you doing this all in one serialisable transaction,
>though?  If not, what guarantee will you have that new data won't
>mess up your row numbering from query to query?

I seee...when new data will be added to table, then row numbers wouldn''t be valid anymore.

Hmmm...looked like I need to find other solution.

thanks

Trigve




      
____________________________________________________________________________________Get the free Yahoo! toolbar and
restassured with the added security of spyware protection.
 
http://new.toolbar.yahoo.com/toolbar/features/norton/index.php

Trigve Siver wrote:
> 
> I seee...when new data will be added to table, then row numbers wouldn''t be valid anymore.
> 
> Hmmm...looked like I need to find other solution.

Try explaining what it is you're trying to achieve, and maybe someone 
will be able to suggest a solution.

--   Richard Huxton  Archonet Ltd

>----- Original Message ----
>From: Richard Huxton <dev@archonet.com>
>To: Trigve Siver <trigves@yahoo.com>
>Cc: pgsql-sql@postgresql.org
>Sent: Wednesday, May 30, 2007 9:05:09 PM
>Subject: Re: [SQL] slow query execution
>
>Trigve Siver wrote:
>> 
>> I seee...when new data will be added to table, then row numbers wouldn''t be valid anymore.
>> 
>> Hmmm...looked like I need to find other solution.
>
>Try explaining what it is you're trying to achieve, and maybe someone 
>will be able to suggest a solution.

I need to perform search on some query (which is already shown in Listview control) and 
highlight items that meet search criteria.

thanks 

Trigve






____________________________________________________________________________________
Don't pick lemons.
See all the new 2007 cars at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html 

Thanks for reply,

This solution looks promising. I'll look at it and test it and let you know.

Thanks once more

Trigve

----- Original Message ----
From: Richard Huxton <dev@archonet.com>
To: Trigve Siver <trigves@yahoo.com>
Sent: Thursday, May 31, 2007 10:33:40 AM
Subject: Re: [SQL] slow query execution

Trigve Siver wrote:
> Hi, thanks for reply
> 
> No, I'm working with c++ and libpqxx (pgsql c++ binding). I'm using
> Win32 Listview control with LS_OWNERDATA style. I can use std::map to
> map row_number to ID field but then I must fetch all records from
> that table. This could be ineffective when table has about 10.000+
> records and user want to view/search only first 100 records.

So - you want something like:

The user runs a query ("all blue things") and that gives a list of 
results. They can then filter those results further ("shape=round") and 
you want to highlight those elements that match.

You either can't or don't want to filter in the application, rather you 
would like to run this as two queries but need to match up results from 
the second query with the first query (your list).

Suggestion:

For the first query, make sure you have the relevant primary key columns 
in your query and do:  CREATE TEMPORARY TABLE my_results AS SELECT ...
Then, you can join against that table in the second query. The temporary 
table will exist until you disconnect - see CREATE TABLE for details.

--   Richard Huxton  Archonet Ltd




      
____________________________________________________________________________________Ready for the edge of your seat? 
Check out tonight's top picks on Yahoo! TV. 
http://tv.yahoo.com/

Trigve Siver wrote:
> ----- Original Message ----
> From: Richard Huxton <dev@archonet.com>
> To: Trigve Siver <trigves@yahoo.com>
> Sent: Thursday, May 31, 2007 10:33:40 AM
> Subject: Re: [SQL] slow query execution
> 
>> So - you want something like:
>>
>> The user runs a query ("all blue things") and that gives a list of 
>> results. They can then filter those results further ("shape=round") and 
>> you want to highlight those elements that match.
>>
>> You either can't or don't want to filter in the application, rather you 
>> would like to run this as two queries but need to match up results from 
>> the second query with the first query (your list).
>>
>> Suggestion:
>>
>> For the first query, make sure you have the relevant primary key columns 
>> in your query and do:
>>   CREATE TEMPORARY TABLE my_results AS SELECT ...
>> Then, you can join against that table in the second query. The temporary 
>> table will exist until you disconnect - see CREATE TABLE for details.
> 
> I think that I can use temporary tables with my previous soultion. As you mentioned,
> I can create temp table with the select ("all blue things")[main select]. (I think I can also add row_numbers 
> to each record as I want to jump to first record in my list which satisfy ("shape=round") 
> condition) When ("shape=round") Query will be made and the: 
> 
> a)I can make join in this the query with my temp table (as you mentioned)
> 
> b)I can make this query on temp table. But the temporary table haven't indexes.

You can add indexes if you want them. You'll also want to run analyze 
against the temp table.

> But when main select is some complicated select then (b) can be used.
> 
> thanks
> 
> Trigve
> 


--   Richard Huxton  Archonet Ltd