Re: [SQL] getting count for a specific querry
On Apr 8, 2005, at 3:37 PM, Joel Fradkin wrote: I don't think my clients would like me to aprox as it is a count of their records. What I plan on doing assuming I can get all my other problems fixed (as mentioned I am going to try and get paid help to see if I goofed it up some where) is make the count a button, so they don't wait everytime, but can choose to wait if need be, maybe I can store the last count with a count on day for the generic search it defaults to, and just have them do a count on demand if they have a specific query. Our screens have several criteria fields in each application. Here is an interface idea I'm working on for displaying query results in PostgreSQL. Maybe it will work for you if your connection method does not prevent you from using cursors. I create a cursor an then fetch the first 1000 rows. The status display has 4 paging buttons, something like this: |< < rows 1 - 1000 of ? > >| The user can hit the "next" button to get the next 1000. If less than 1000 are fetched the ? is replaced with the actual count. They can press the "last" button to move to the end of the cursor and get the actual count if they need it. So here the initial query should be fast, the user can get the count if they need it, and you don't have to re-query using limit and offset. The problem I'm looking into now (which I just posted on the general list) is I don't see a way to get the table and column information from a cursor. If I fetch from a cursor, the table OID and column number values are 0 in the row description. If I execute the same query directly without a cursor, the row description has the correct values for table OID and column number. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] getting count for a specific querry
On Apr 9, 2005 10:00 AM, John DeSoi <[EMAIL PROTECTED]> wrote:On Apr 8, 2005, at 3:37 PM, Joel Fradkin wrote:> I don't think my clients would like me to aprox as it is a count of> their> records. What I plan on doing assuming I can get all my other problems> fixed> (as mentioned I am going to try and get paid help to see if I goofed> it up> some where) is make the count a button, so they don't wait everytime,> but> can choose to wait if need be, maybe I can store the last count with a> count> on day for the generic search it defaults to, and just have them do a> count> on demand if they have a specific query. Our screens have several> criteria> fields in each application.Here is an interface idea I'm working on for displaying query resultsin PostgreSQL. Maybe it will work for you if your connection methoddoes not prevent you from using cursors. I create a cursor an thenfetch the first 1000 rows. The status display has 4 paging buttons,something like this:|< < rows 1 - 1000 of ? > >|The user can hit the "next" button to get the next 1000. If less than1000 are fetched the ? is replaced with the actual count. They canpress the "last" button to move to the end of the cursor and get theactual count if they need it. So here the initial query should be fast,the user can get the count if they need it, and you don't have tore-query using limit and offset.The problem I'm looking into now (which I just posted on the generallist) is I don't see a way to get the table and column information froma cursor. If I fetch from a cursor, the table OID and column numbervalues are 0 in the row description. If I execute the same querydirectly without a cursor, the row description has the correct valuesfor table OID and column number.John DeSoi, Ph.D.http://pgedit.com/Power Tools for PostgreSQL Oracle Forms uses a similar method as you described and it works just fine. It will say Record 1 of ?(But I think the developer can set the amount of records cached so that if you set it to 10 and queried 5 records it would say record 1 of 5 because it would be under the cache amount.) Forms also offers a button that say get hit count. So if you really need to know the record count you can get it without moving off the current record.
Re: [SQL] getting count for a specific querry
On Apr 9, 2005, at 11:43 AM, Bob Henkel wrote: Forms also offers a button that say get hit count. So if you really need to know the record count you can get it without moving off the current record. That's a good idea too. Maybe in my interface you could click on the ? to get the count without changing the rows you are viewing. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] subselect query time and loops problem
hi, I am using this query. SELECT * FROM guild_properties_buy WHERE agent IN (SELECT agent_id FROM guild_agents WHERE address_region = 'midlands' AND active='on' AND status=0) AND country = 'United Kingdom' AND active='on' AND status='0' ORDER BY price DESC LIMIT 10 OFFSET 0 when i run this in my development server i get this. Limit (cost=1680331.30..1680331.31 rows=1 width=541) (actual time=3631.97..3632.00 rows=10 loops=1) -> Sort (cost=1680331.30..1680331.31 rows=1 width=541) (actual time=3631.96..3631.98 rows=11 loops=1) Sort Key: price -> Seq Scan on guild_properties_buy (cost=0.00..1680331.29 rows=1 width=541) (actual time=39.39..3556.80 rows=4747 loops=1) Filter: ((country = 'United Kingdom'::character varying) AND (active = 'on'::character varying) AND (status = 0) AND (subplan)) SubPlan -> Materialize (cost=57.15..57.15 rows=1 widt h=4) (actual time=0.00..0.05 rows=88 loops=27235) -> Seq Scan on guild_agents (cost=0.00..57.15 rows=1 width=4) (actual time=0.04..1.76 rows=100 loops=1) Filter: ((address_region = 'midlands'::character varying) AND (active = 'on'::character varying) AND (status = 0))Total runtime: 3633.46 msec when i run this in my hosting server i get this. Limit (cost=847964.41..847964.43 rows=10 width=1036) (actual time=28265.15..28265.19 rows=10 loops=1) -> Sort (cost=847964.41..847999.30 rows=13957 width=1036) (actual time=28265.15..28265.17 rows=11 loops=1) Sort Key: price -> Seq Scan on guild_properties_buy (cost=0.00..832943.58 rows=13957 width=1036) (actual time=6.88..28157.11 rows=4790 loops=1) Filter: ((country = 'United Kingdom'::character varying) AND (active = 'on'::character varying) AND (status = 0) AND (subplan)) SubPlan -> Seq Scan on guild_agents (cost=0.00..56.15 rows=100 width=4) (actual time=0.01..0.95 rows=87 loops=27173) Filter: ((address_region = 'midlands'::character varying) AND (active = 'on'::character varying) AND (status = 0))Total runtime: 28269.32 msec when i use my script to replace sub query then both servers run fine. SELECT * FROM guild_properties_buy WHERE agent IN ( 56259,56397..) AND country = 'United Kingdom' AND active='on' AND status='0' ORDER BY price DESC LIMIT 10 OFFSET 0 Both servers have same version with same postgresql.conf. But the time taken by both the servers with subselect are way different. the loops and the query time are completely different. both servers have same indexes. Any help will be greately appreciated.. Best Regards Pankaj Naug Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
Re: [SQL] subselect query time and loops problem
pankaj naug <[EMAIL PROTECTED]> writes: > Both servers have same version with same postgresql.conf. But the time taken > by both the servers with subselect are way different. the loops and the query > time are completely different. both servers have same indexes. Evidently one has been analyzed much more recently than the other, because the estimated row counts are wildly different. You didn't say which PG version this is, but I gather that it's pre-7.4, which means that the performance of IN (SELECT ...) is generally going to be awful. Either rewrite as a join or update to 7.4 or later. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Question on triggers and plpgsql
I think I sent my previous message to John only (sorry!) I just wanted to double check one detail that is not explicitly stated in the documentation for createlang. My question is: can I use createlang on a database that is currently active? That is, a database with plenty of tables that has been and is currently in use? My guess is that there should be no problem and no risk in doing that -- but being my first steps in PL, I wouldn't like to trust a beginner's intuition for a production system. Thanks, Carlos -- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question on triggers and plpgsql
Carlos Moreno <[EMAIL PROTECTED]> writes: > My question is: can I use createlang on a database that is > currently active? That is, a database with plenty of tables > that has been and is currently in use? Sure. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
