Lists: | pgsql-general |
---|
From: | "Silas Justiniano" <silasju(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | What's faster? |
Date: | 2006-02-09 03:45:15 |
Message-ID: | 1139456715.177179.159300@o13g2000cwo.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hello all!
I'm performing a query that returns me hundreds of records... but I
need cut them in pages that have 15 items! (using PHP)
So, is it faster:
select blablabal from _complex_query
if (count($result) > 15) show_pages;
show_only_15_rows($result);
or:
select count(*) from _complex_query
if ($result1 > 15) show_pages;
select blablabal from _complex_query LIMIT ... (see the LIMIT!)
show $result
On the first, I can use pg_num_rows instead of count(), too.
what do you think?
Thank you!
From: | Chris <dmagick(at)gmail(dot)com> |
---|---|
To: | Silas Justiniano <silasju(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: What's faster? |
Date: | 2006-02-09 03:49:52 |
Message-ID: | 43EABBE0.7080600@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hi,
Second option.
For the first option, if your query returns say 10,000 rows then php has
to actually fetch 10,000 rows from the database before anything else
happens even though you're only displaying 15.
Silas Justiniano wrote:
> Hello all!
>
> I'm performing a query that returns me hundreds of records... but I
> need cut them in pages that have 15 items! (using PHP)
>
> So, is it faster:
>
> select blablabal from _complex_query
> if (count($result) > 15) show_pages;
> show_only_15_rows($result);
>
> or:
>
> select count(*) from _complex_query
> if ($result1 > 15) show_pages;
> select blablabal from _complex_query LIMIT ... (see the LIMIT!)
> show $result
>
> On the first, I can use pg_num_rows instead of count(), too.
>
> what do you think?
From: | Alban Hertroys <alban(at)magproductions(dot)nl> |
---|---|
To: | Silas Justiniano <silasju(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: What's faster? |
Date: | 2006-02-09 15:50:28 |
Message-ID: | 43EB64C4.2090003@magproductions.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Silas Justiniano wrote:
> Hello all!
>
> I'm performing a query that returns me hundreds of records... but I
> need cut them in pages that have 15 items! (using PHP)
>
> So, is it faster:
>
> select blablabal from _complex_query
> if (count($result) > 15) show_pages;
> show_only_15_rows($result);
>
> or:
>
> select count(*) from _complex_query
> if ($result1 > 15) show_pages;
> select blablabal from _complex_query LIMIT ... (see the LIMIT!)
> show $result
If you don't care about how many pages you'll get, try:
select blablabal from _complex_query LIMIT (15+1);
if (count($result) > 15) show_pages;
show_only_15_rows($result);
--
Alban Hertroys
alban(at)magproductions(dot)nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
//Showing your Vision to the World//
From: | "Uwe C(dot) Schroeder" <uwe(at)oss4u(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | "Silas Justiniano" <silasju(at)gmail(dot)com> |
Subject: | Re: What's faster? |
Date: | 2006-02-09 18:52:03 |
Message-ID: | 200602091052.03440.uwe@oss4u.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Depending on your keys neither.
Rather let the DB handle the resultset. count(*) is quite slow.
How about something like
select blablabla from _complex_query order by _key_ (optional DESC or ASC)
OFFSET xxx LIMIT 15
where your offset would be a parameter from the php side and is basically the
page number of the number of pages you want to display.
The only drawback of that is that you will never see the total number of hits.
So maybe you do a count(*) ONCE and then use the above query to loop over the
resultset - or you don't show the number of pages and just have a "next
results" and "previous results" button that adjusts the offset parameter.
On Wednesday 08 February 2006 19:45, Silas Justiniano wrote:
> Hello all!
>
> I'm performing a query that returns me hundreds of records... but I
> need cut them in pages that have 15 items! (using PHP)
>
> So, is it faster:
>
> select blablabal from _complex_query
> if (count($result) > 15) show_pages;
> show_only_15_rows($result);
>
> or:
>
> select count(*) from _complex_query
> if ($result1 > 15) show_pages;
> select blablabal from _complex_query LIMIT ... (see the LIMIT!)
> show $result
>
> On the first, I can use pg_num_rows instead of count(), too.
>
> what do you think?
>
> Thank you!
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
UC
--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416
From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | "Uwe C(dot) Schroeder" <uwe(at)oss4u(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org, Silas Justiniano <silasju(at)gmail(dot)com> |
Subject: | Re: What's faster? |
Date: | 2006-02-09 19:38:11 |
Message-ID: | 20060209193811.GE14852@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thu, Feb 09, 2006 at 10:52:03AM -0800, Uwe C. Schroeder wrote:
> Depending on your keys neither.
> Rather let the DB handle the resultset. count(*) is quite slow.
>
> How about something like
>
> select blablabla from _complex_query order by _key_ (optional DESC or ASC)
> OFFSET xxx LIMIT 15
>
> where your offset would be a parameter from the php side and is basically the
> page number of the number of pages you want to display.
> The only drawback of that is that you will never see the total number of hits.
> So maybe you do a count(*) ONCE and then use the above query to loop over the
> resultset - or you don't show the number of pages and just have a "next
> results" and "previous results" button that adjusts the offset parameter.
Another possibility is to put a limit of 151. If you get 151 rows you
print 1 2 3 .. 8 9 10 More. If you get less you know how many pages. As
you get to page 5 you can limit to 225+1.
Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
From: | "Gonzalo Villegas" <chalo1970(at)hotmail(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Insert more than one t-uple in a single sql |
Date: | 2006-02-09 22:57:03 |
Message-ID: | BAY112-DAV19105DCB6C39371A6D3BFFA9030@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hi all,
I'm trying to insert more than one t-uple in a single sql query. Just like
copy table (field1,field2,...) from ....
It must be something like
insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
(c1,c2,...)
Thanks in advance!
Gonzalo A. Villegas
From: | Chris <dmagick(at)gmail(dot)com> |
---|---|
To: | Gonzalo Villegas <chalo1970(at)hotmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Insert more than one t-uple in a single sql |
Date: | 2006-02-09 23:13:30 |
Message-ID: | 43EBCC9A.6030408@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hi,
You can't do that in postgres, sorry. That's a mysql-ism.
Gonzalo Villegas wrote:
> It must be something like
>
> insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
> (c1,c2,...)
From: | Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au> |
---|---|
To: | Gonzalo Villegas <chalo1970(at)hotmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Insert more than one t-uple in a single sql |
Date: | 2006-02-09 23:26:06 |
Message-ID: | 43EBCF8E1A.1A7AKG@129.180.47.120 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thu, 9 Feb 2006 17:57:03 -0500, "Gonzalo Villegas" <chalo1970(at)hotmail(dot)com> wrote:
>
>
> Hi all,
>
> I'm trying to insert more than one t-uple in a single sql query. Just like
>
> copy table (field1,field2,...) from ....
>
> It must be something like
>
> insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
> (c1,c2,...)
insert into table (field1,field2)
select v1,v2
union all
select b1,b2
union all
select c1,c2
klint.
+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg(at)kgb(dot)une(dot)edu(dot)au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+
From: | Rick Gigger <rick(at)alpinenetworking(dot)com> |
---|---|
To: | Chris <dmagick(at)gmail(dot)com> |
Cc: | Gonzalo Villegas <chalo1970(at)hotmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Insert more than one t-uple in a single sql |
Date: | 2006-02-09 23:29:26 |
Message-ID: | 888D331E-3142-4CC0-827C-B8F1E5D14248@alpinenetworking.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
There is a little trick you can do though, it goes something like this:
insert into table (field1, field2, field3) select v1, v2, v3 union
b1, b2, b3 union select c1, c2, c3
I originally did this because it was significantly faster on SQL
Server 2000 than doing the inserts individually. Usually I did it
with up to maybe 20 rows at a time that were all grouped to some kind
of common parent.
Some version of postgres a long time ago broke my code because it did
some stricter type checking and so I had to make sure that I was
never putting single quotes around int and that date fields were
strictly typecasted so that it wouldn't think they were strings. It
does work now though as long as I do that and I use it all the time.
I don't know if it gets the same sort of speed boost in postgres as
it did in sql server. As long as they are all done within a single
transaction in postgres it may not matter whether you do them
individually or batched like that.
If you really have a lot of data you want to insert at once why not
just use COPY?
Rick
On Feb 9, 2006, at 4:13 PM, Chris wrote:
> Hi,
>
> You can't do that in postgres, sorry. That's a mysql-ism.
>
> Gonzalo Villegas wrote:
>
>> It must be something like
>> insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
>> (c1,c2,...)
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Rick Gigger <rick(at)alpinenetworking(dot)com> |
Cc: | Chris <dmagick(at)gmail(dot)com>, Gonzalo Villegas <chalo1970(at)hotmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Insert more than one t-uple in a single sql |
Date: | 2006-02-10 00:12:45 |
Message-ID: | 6116.1139530365@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
>> You can't do that in postgres, sorry. That's a mysql-ism.
>>
>> Gonzalo Villegas wrote:
>>
>>> It must be something like
>>> insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
>>> (c1,c2,...)
Actually, that's not a mysql-ism, it's SQL-spec syntax. We haven't got
round to implementing it, partly because the SELECT ... UNION ALL ...
syntax provides a perfectly good substitute. It is on the TODO list
though.
I wouldn't recommend trying to insert more than a few dozen rows with
the UNION ALL approach, else the planner overhead might swamp any
savings. If you want to insert thousands of rows at once, you almost
certainly want to find a way to use COPY.
regards, tom lane
From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Rick Gigger <rick(at)alpinenetworking(dot)com>, Chris <dmagick(at)gmail(dot)com>, Gonzalo Villegas <chalo1970(at)hotmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Insert more than one t-uple in a single sql |
Date: | 2006-02-10 01:11:57 |
Message-ID: | 20060210011157.GC15836@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thu, Feb 09, 2006 at 07:12:45PM -0500, Tom Lane wrote:
> >> You can't do that in postgres, sorry. That's a mysql-ism.
> >>
> >> Gonzalo Villegas wrote:
> >>
> >>> It must be something like
> >>> insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
> >>> (c1,c2,...)
>
> Actually, that's not a mysql-ism, it's SQL-spec syntax. We haven't
> got round to implementing it, partly because the SELECT ... UNION
> ALL ... syntax provides a perfectly good substitute. It is on the
> TODO list though.
Another way it's different from COPY is that the VALUES can take
expressions.
What all would need to change in order to implement this? There
appear to be things in src/backend/parser and src/bin/psql that bear
on this. Would libpq and ecpg need to change?
Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
From: | "Silas Justiniano" <silasju(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Insert more than one t-uple in a single sql |
Date: | 2006-02-12 19:27:32 |
Message-ID: | 1139772452.234349.181530@g43g2000cwa.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Thank you very much. But what do you think of:
select foobar from table where <condition>;
if (pgsql_num_rows($result) > 15) show_pages;
show_only_15($result);
Thank you!
From: | Chris <dmagick(at)gmail(dot)com> |
---|---|
To: | Silas Justiniano <silasju(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Insert more than one t-uple in a single sql |
Date: | 2006-02-12 23:57:14 |
Message-ID: | 43EFCB5A.4060009@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
What happens if that query returns 5,000 rows?
Postgres does actually have to fetch all of those rows, and then php has
to allocate memory etc to store them.
It's not very efficient.
Silas Justiniano wrote:
> Thank you very much. But what do you think of:
>
> select foobar from table where <condition>;
> if (pgsql_num_rows($result) > 15) show_pages;
> show_only_15($result);