[PERFORM] planner and worst case scenario
Here is my query, that returns one row: SELECT f1, f2,(SELECT dfield FROM d WHERE d.ukey = f1) FROM m WHERE status IN(2) AND jid IN(17674) ORDER BY pkey DESC LIMIT 25 OFFSET 0; Here was the really bad plan chosen. This didn't come back for a long while and had to be cancelled: QUERY PLAN -- Limit (cost=0.00..10493.05 rows=25 width=118) -> Index Scan Backward using m_pkey on m (cost=0.00..1883712.97 rows=4488 width=118) Filter: ((status = 2) AND (jid = 17674)) SubPlan -> Index Scan using d_pkey on d (cost=0.00..3.83 rows=1 width=24) Index Cond: (ukey = $0) (6 rows) After an ANALYZE the plan was much better: QUERY PLAN -- Limit (cost=22060.13..22060.19 rows=25 width=119) -> Sort (cost=22060.13..22067.61 rows=2993 width=119) Sort Key: serial -> Index Scan using m_jid_uid_key on m (cost=0.00..21887.32 rows=2993 width=119) Index Cond: (jid = 17674) Filter: (status = 2) SubPlan -> Index Scan using d_pkey on d (cost=0.00..3.83 rows=1 width=24) Index Cond: (ukey = $0) (9 rows) The thing is since there was only 1 row in the (very big) table with that jid, the ANALYZE didn't include that row in the stats table, so I'm figuring there was a small random change that made it choose the better query. Doing: ALTER TABLE m ALTER jid SET STATISTICS 1000; produce a much more accurate row guess: QUERY PLAN -- Limit (cost=2909.65..2909.71 rows=25 width=115) -> Sort (cost=2909.65..2910.64 rows=395 width=115) Sort Key: serial -> Index Scan using m_jid_uid_key on m (cost=0.00..2892.61 rows=395 width=115) Index Cond: (jbid = 17674) Filter: (status = 2) SubPlan -> Index Scan using d_pkey on d (cost=0.00..3.83 rows=1 width=24) Index Cond: (userkey = $0) (9 rows) It seems the problem is that the pg planner goes for the job with the lowest projected time, but ignores the worst case scenario. I think the odds of this problem happening again are lower since the SET STATISTICS, but I don't know what triggered the really bad plan in the first place. Did pg think that because so many rows would match the limit would be filled up soon, so that a more accurate and lower assumption would cause it to choose the better plan? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] How can one see what queries are running withing a
On Sat, Jun 26, 2004 at 04:58:16PM +0800, Christopher Kings-Lynne wrote: > > >>Let see in contrib/ the application pg_who ... you will see the process, > >>the queries, and the CPU ... ;o) > > Even easier: > > SELECT * FROM pg_stat_activity; > > As a superuser. Thanks! That works as needed! Best regards, Pascal > > Chris > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Query performance
Usually, when you post a request like this, you should provide something a little more concrete (the CREATE TABLE statement for that table, with Since you didn't, I'll posit something that sounds like what you're using, and take a stab at your problem. TABLE Prices ( stock VARCHAR(9) ,asof DATE, ,opening MONEY ,closing MONEY ,PRIMARY KEY (stock, asof) ) SELECT stock, AVG((closing-opening)/opening) as ratio FROM Prices GROUP BY stock ORDER BY ratio DESC LIMIT 10; -- top 10 best-performing stocks. ""Bill"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... Actually, I have some queries that are slow, however I was wondering if you could help me write a query that is rather simple, but I, as a true database novice, can't seem to conjure. So we have stocks, as I have previously said, and I have a huge table which contains all of the opening and closing prices of some stocks from each day. What I like to do, in English, for each stock in each day is find a ratio: abs(closing-opening)/opening. Then I would like to average all of the ratios of each day of each individual stock together to find a final ratio for each stock, then I would like to find the highest average, to find the best performing stock. So what query can I use, and (as is appropriate for this group), how can it be optimized to run the fastest?
Re: [PERFORM] How can one see what queries are running withing a
P.A.M. van Dam wrote: Hi! I'd like to know if there is a way to see what queries are running within a certain postgres instance and how much resources (cpu/memory) etc. they are using. Right now it's impossible to see what is happening within postgres when it's binaries are using 100% CPU. In Sybase there is a command which let's you view what 'processes' are running within the server and how much cpu (according to Sybase) they are using. It also provides you with a stored procedure to kill off some bad behaving queries. How can one do this within postgres? Thanks in advance! Best regards, Pascal ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster select * from pg_stat_activity. If you want to see the command that was run, you will need to turn on stats_command_string = true in postgresql.conf and re-start server. PID shows up, so you can kill bad queries from terminal and see CUP % in top Roger Ging V.P., Information Technology Music Reports, Inc. ---(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
Re: [PERFORM] reindex and copy - deadlock?
Litao Wu <[EMAIL PROTECTED]> writes: > Since there are multiple databases and > there are 170 postgres processes this morning, > 60 of them are access the problem database, > and 57 of 60 are non-idle. > We only need to gdb those 57 processes, or > we need gdb 60 or 170? Potentially the deadlock could be anywhere :-(. You should definitely not assume it must be one of the processes connected to the problem database, because the buffer pool is cluster-wide. Might be worth setting up a shell script to help. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] reindex and copy - deadlock?
Thanks! OK, we will do this exceise next time. TSince there are multiple databases and there are 170 postgres processes this morning, 60 of them are access the problem database, and 57 of 60 are non-idle. We only need to gdb those 57 processes, or we need gdb 60 or 170? Thanks again! --- Tom Lane <[EMAIL PROTECTED]> wrote: > Litao Wu <[EMAIL PROTECTED]> writes: > > Our PG version is 7.3.2. > > Hmm. On general principles you should be using > 7.3.6, but I do not see > anything in the 7.3.* change logs that looks very > likely to cure this. > > > The copy process is always there. Besides copy > > process, there are many select processes wait also > > (it is understandable only when reindex, > > but how come selects wait when drop/create index? > > DROP INDEX would lock out selects (it has no other > way to be sure no > select is trying to *use* the index). Once you're > past that, selects > would work, but if you try something like > begin; drop index; create index; commit; > then the drop's lock will be held till commit. > > I'm not sure about whether COPY is related. In your > original post, the > COPY was waiting to acquire RowExclusiveLock on the > table, so it hadn't > actually done anything yet and really couldn't be > holding a buffer lock > AFAICS. > > > But one thing is sure: > > reindex or create index is granted lock while > > others wait. If reindex/create index is not > > the perpetrator, how can PG grants it lock > > but not others, like COPY? > > The point is that it's waiting for a lower-level > lock (namely a buffer > LWLock). There's no deadlock detection for LWLocks, > because they're not > supposed to be used in ways that could cause a > deadlock. > > Assuming for the moment that indeed this is a > deadlock, you could learn > something the next time it happens with some manual > investigation. > You'll need to keep using the debug-enabled build. > When you next get a > lockup, proceed as follows: > > 1. Attach to the REINDEX or CREATE INDEX process and > find out which > LWLock number it is blocked on. (This is the lockid > argument of > LWLockAcquire, 21335 in your trace of today.) > > 2. For *each* live backend process (including the > REINDEX itself), > attach with gdb and look at the held-locks status of > lwlock.c. > This would go something like > > gdb> p num_held_lwlocks > if greater than zero: > gdb> x/10d held_lwlocks > (replace "10" by the value of num_held_lwlocks) > > If you find a backend that is holding the lock > number that REINDEX > wants, print out its call stack with "bt", and look > in pg_locks to see > what lockmanager locks it is holding or waiting for. > If you do not find > one, then the deadlock theory is disproved, and > we're back to square > one. > > regards, tom lane > > ---(end of > broadcast)--- > TIP 8: explain analyze is your friend > __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] reindex and copy - deadlock?
Litao Wu <[EMAIL PROTECTED]> writes: > Our PG version is 7.3.2. Hmm. On general principles you should be using 7.3.6, but I do not see anything in the 7.3.* change logs that looks very likely to cure this. > The copy process is always there. Besides copy > process, there are many select processes wait also > (it is understandable only when reindex, > but how come selects wait when drop/create index? DROP INDEX would lock out selects (it has no other way to be sure no select is trying to *use* the index). Once you're past that, selects would work, but if you try something like begin; drop index; create index; commit; then the drop's lock will be held till commit. I'm not sure about whether COPY is related. In your original post, the COPY was waiting to acquire RowExclusiveLock on the table, so it hadn't actually done anything yet and really couldn't be holding a buffer lock AFAICS. > But one thing is sure: > reindex or create index is granted lock while > others wait. If reindex/create index is not > the perpetrator, how can PG grants it lock > but not others, like COPY? The point is that it's waiting for a lower-level lock (namely a buffer LWLock). There's no deadlock detection for LWLocks, because they're not supposed to be used in ways that could cause a deadlock. Assuming for the moment that indeed this is a deadlock, you could learn something the next time it happens with some manual investigation. You'll need to keep using the debug-enabled build. When you next get a lockup, proceed as follows: 1. Attach to the REINDEX or CREATE INDEX process and find out which LWLock number it is blocked on. (This is the lockid argument of LWLockAcquire, 21335 in your trace of today.) 2. For *each* live backend process (including the REINDEX itself), attach with gdb and look at the held-locks status of lwlock.c. This would go something like gdb> p num_held_lwlocks if greater than zero: gdb> x/10d held_lwlocks (replace "10" by the value of num_held_lwlocks) If you find a backend that is holding the lock number that REINDEX wants, print out its call stack with "bt", and look in pg_locks to see what lockmanager locks it is holding or waiting for. If you do not find one, then the deadlock theory is disproved, and we're back to square one. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] reindex and copy - deadlock?
Hi Tom, Our PG version is 7.3.2. The copy process is always there. Besides copy process, there are many select processes wait also (it is understandable only when reindex, but how come selects wait when drop/create index? >From Postgres doc: Note: Another approach to dealing with a corrupted user-table index is just to drop and recreate it. This may in fact be preferable if you would like to maintain some semblance of normal operation on the table meanwhile. REINDEX acquires exclusive lock on the table, while CREATE INDEX only locks out writes not reads of the table. ) Each time, whan this happened, it might hang on the different index. But one thing is sure: reindex or create index is granted lock while others wait. If reindex/create index is not the perpetrator, how can PG grants it lock but not others, like COPY? Forgive me I had not provided the full table and index names, IP address, etc. for security reason. Here is the copy of my the first post on June 8: Hi, We often experience with the problem that reindex cannot be finished in our production database. It's typically done with 30 minutes. However, sometimes, when there is another "COPY" process, reindex will not finish. By monitoring the CPU time reindex takes, it does not increase at all. That seems a deadlock. But the following query shows only reindex process (23127)is granted lock while COPY process (3149) is not. Last time when we have this problem and kill reindex process and COPY process does not work. We had to bounce the database server. As you know, when reindex is running, nobody can access the table. Can someone kindly help? Thanks, Here is lock info from database: replace| database | transaction | pid |mode | granted ---+--+-+---+-+- email |17613 | | 3149 | RowExclusiveLock| f email_cre_dom_idx |17613 | | 23127 | ExclusiveLock | t email_cid_cre_idx |17613 | | 23127 | ShareLock | t email_cid_cre_idx |17613 | | 23127 | AccessExclusiveLock | t email |17613 | | 23127 | ShareLock | t email |17613 | | 23127 | AccessExclusiveLock | t email_cid_cre_dom_idx |17613 | | 23127 | ShareLock | t email_cid_cre_dom_idx |17613 | | 23127 | AccessExclusiveLock | t email_did_cre_idx |17613 | | 23127 | ShareLock | t email_did_cre_idx |17613 | | 23127 | AccessExclusiveLock | t email_cre_dom_idx |17613 | | 23127 | AccessExclusiveLock | t (11 rows) Here are the processes of 3149 and 23127 from OS: postgres 3149 1.3 6.4 154104 13 ? S Jun03 92:04 postgres: postgres db1 xx.xx.xx.xx COPY waiting postgres 23127 3.2 9.3 228224 194512 ? S 03:35 15:03 postgres: postgres db1 [local] REINDEX Here are queries from database: 23127 | REINDEX table email 3149 | COPY email (...) FROM stdin --- Tom Lane <[EMAIL PROTECTED]> wrote: > Litao Wu <[EMAIL PROTECTED]> writes: > > It happened again. > > This time it hangs when we drop/create index. > > Here is gdb info with --enable-debug postgres. > > Well, that pretty much removes all doubt: something > has left the buffer > context lock (cntx_lock) set on a buffer that > certainly ought to be free. > > The problem here is that REINDEX (or CREATE INDEX in > this case) is the > victim, not the perpetrator, so we still don't know > exactly what's > causing the error. We need to go backwards in time, > so to speak, to > identify the code that's leaving the buffer locked > when it shouldn't. > I don't offhand have a good idea about how to do > that. Is there another > process that is also getting stuck when REINDEX does > (if so please get > a backtrace from it too)? > > BTW, what Postgres version are you using again? The > line numbers in > your trace don't square with any current version of > bufmgr.c ... > > regards, tom lane > > ---(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 > __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] postgres 7.4 at 100%
Title: RE: [PERFORM] postgres 7.4 at 100% Creating indexes on a table affects insert performance depending on the number of indexes that have to be populated. From a query standpoint, indexes are a godsend in most cases. Duane -Original Message- From: Chris Cheston [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 30, 2004 12:19 AM To: Gavin M. Roy Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] postgres 7.4 at 100% Oh my, creating an index has absolutely reduced the times it takes to query from around 700 ms to less than 1 ms! Thanks so much for all your help. You've saved me! One question: Why would I or would I not create multiple indexes in a table? I created another index in the same table an it's improved performance even more. Thanks, Chris On Tue, 29 Jun 2004 09:03:24 -0700, Gavin M. Roy <[EMAIL PROTECTED]> wrote: > > Is the from field nullable? If not, try "create index calllogs_from on > calllogs ( from );" and then do an explain analyze of your query. > > Gavin > > > > Chris Cheston wrote: > > >ok i just vacuumed it and it's taking slightly longer now to execute > >(only about 8 ms longer, to around 701 ms). > > > >Not using indexes for calllogs(from)... should I? The values for > >calllogs(from) are not unique (sorry if I'm misunderstanding your > >point). > > > >Thanks, > > > >Chris > > > >On Tue, 29 Jun 2004 16:21:01 +0800, Christopher Kings-Lynne > ><[EMAIL PROTECTED]> wrote: > > > > > >>>live=# explain analyze SELECT id FROM calllogs WHERE from = 'you'; > >>> QUERY PLAN > >>>-- > >>> Seq Scan on calllogs (cost=0.00..136.11 rows=24 width=4) (actual > >>>time=0.30..574.72 rows=143485 loops=1) > >>> Filter: (from = 'you'::character varying) > >>> Total runtime: 676.24 msec > >>>(3 rows) > >>> > >>> > >>Have you got an index on calllogs(from)? > >> > >>Have you vacuumed and analyzed that table recently? > >> > >>Chris > >> > >> > >> > >> > > > >---(end of broadcast)--- > >TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] reindex and copy - deadlock?
Litao Wu <[EMAIL PROTECTED]> writes: > It happened again. > This time it hangs when we drop/create index. > Here is gdb info with --enable-debug postgres. Well, that pretty much removes all doubt: something has left the buffer context lock (cntx_lock) set on a buffer that certainly ought to be free. The problem here is that REINDEX (or CREATE INDEX in this case) is the victim, not the perpetrator, so we still don't know exactly what's causing the error. We need to go backwards in time, so to speak, to identify the code that's leaving the buffer locked when it shouldn't. I don't offhand have a good idea about how to do that. Is there another process that is also getting stuck when REINDEX does (if so please get a backtrace from it too)? BTW, what Postgres version are you using again? The line numbers in your trace don't square with any current version of bufmgr.c ... regards, tom lane ---(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
Re: [PERFORM] reindex and copy - deadlock?
Hi All, It happened again. This time it hangs when we drop/create index. Here is gdb info with --enable-debug postgres. Thank you for your help! postgres 24533 24327 2 Jun28 ?00:39:11 postgres: postgres xxx xxx.xxx.x.xxx COPY waiting postgres 23508 24327 0 03:23 ?00:00:00 postgres: postgres xxx xxx.xxx.x.xx SELECT waiting root 23662 22727 0 03:24 ?00:00:00 /xxx/bin/psql -t -A -q xxx -U postgres -c set sort_mem=131072; DROP INDEX xxx_mod_ac_did_cre_idx; CREATE INDEX xxx_mod_ac_did_cre_idx ON xxx_module_action USING btree (domain_id, created); postgres 23663 24327 2 03:24 ?00:04:40 postgres: postgres xxx [local] CREATE INDEX postgres 24252 24327 0 03:26 ?00:00:00 postgres: postgres xxx xxx.xxx.x.xx SELECT waiting bash-2.05a$ gdb /xxx/bin/postgres GNU gdb Red Hat Linux (5.2-2) Copyright 2002 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "i386-redhat-linux"... (gdb) attach 23663 Attaching to program: /xxx/bin.Linux/postgres, process 23663 Reading symbols from /usr/lib/libz.so.1...done. Loaded symbols for /usr/lib/libz.so.1 Reading symbols from /usr/lib/libreadline.so.4...done. Loaded symbols for /usr/lib/libreadline.so.4 Reading symbols from /lib/libtermcap.so.2...done. Loaded symbols for /lib/libtermcap.so.2 Reading symbols from /lib/libcrypt.so.1...done. Loaded symbols for /lib/libcrypt.so.1 Reading symbols from /lib/libresolv.so.2...done. Loaded symbols for /lib/libresolv.so.2 Reading symbols from /lib/libnsl.so.1...done. Loaded symbols for /lib/libnsl.so.1 Reading symbols from /lib/libdl.so.2...done. Loaded symbols for /lib/libdl.so.2 Reading symbols from /lib/i686/libm.so.6...done. Loaded symbols for /lib/i686/libm.so.6 Reading symbols from /lib/i686/libc.so.6...done. Loaded symbols for /lib/i686/libc.so.6 Reading symbols from /lib/ld-linux.so.2...done. Loaded symbols for /lib/ld-linux.so.2 Reading symbols from /lib/libnss_files.so.2...done. Loaded symbols for /lib/libnss_files.so.2 0x420e8bb2 in semop () from /lib/i686/libc.so.6 (gdb) bt #0 0x420e8bb2 in semop () from /lib/i686/libc.so.6 #1 0x080ff954 in PGSemaphoreLock (sema=0x4a2d83e8, interruptOK=0 '\0') at pg_sema.c:434 #2 0x0811635e in LWLockAcquire (lockid=21335, mode=LW_EXCLUSIVE) at lwlock.c:312 #3 0x0810f49e in LockBuffer (buffer=10657, mode=2) at bufmgr.c:1848 #4 0x0807dea3 in _bt_getbuf (rel=0x40141b10, blkno=4294967295, access=2) at nbtpage.c:337 #5 0x080813d8 in _bt_blnewpage (index=0x40141b10, buf=0xbfffe724, page=0xbfffe728, flags=1) at nbtsort.c:188 #6 0x08081692 in _bt_buildadd (index=0x40141b10, state=0x4e0b3e30, bti=0x4fe20cb8) at nbtsort.c:373 #7 0x08081b77 in _bt_load (index=0x40141b10, btspool=0x82bf7b8, btspool2=0x0) at nbtsort.c:638 #8 0x080813b8 in _bt_leafbuild (btspool=0x82bf7b8, btspool2=0x0) at nbtsort.c:171 #9 0x0807e1d0 in btbuild (fcinfo=0xbfffe820) at nbtree.c:165 #10 0x081630d7 in OidFunctionCall3 (functionId=338, arg1=1075019120, arg2=1075059472, arg3=137095072) at fmgr.c:1275 #11 0x08092093 in index_build (heapRelation=0x40137d70, indexRelation=0x40141b10, indexInfo=0x82be7a0) at index.c:1447 #12 0x080913d7 in index_create (heapRelationId=17618, indexRelationName=0x82b9648 "xxx_mod_ac_did_cre_idx", indexInfo=0x82be7a0, accessMethodObjectId=403, classObjectId=0x82be578, primary=0 '\0', isconstraint=0 '\0', allow_system_table_mods=0 '\0') at index.c:765 #13 0x080b88ae in DefineIndex (heapRelation=0x82b9698, indexRelationName=0x82b9648 "xxx_mod_ac_did_cre_idx", accessMethodName=0x82b96c0 "btree", attributeList=0x82b9718, unique=0 '\0', primary=0 '\0', isconstraint=0 '\0', predicate=0x0, rangetable=0x0) at indexcmds.c:211 #14 0x0811b250 in ProcessUtility (parsetree=0x82b9788, dest=Remote, completionTag=0xbfffea80 "") at utility.c:620 #15 0x08118df6 in pg_exec_query_string (query_string=0x82b91e0, dest=Remote, parse_context=0x82ade58) at postgres.c:789 #16 0x08119f0d in PostgresMain (argc=4, argv=0xbfffecb0, username=0x8240679 "postgres") at postgres.c:2013 #17 0x08102078 in DoBackend (port=0x8240548) at postmaster.c:2302 #18 0x081019ca in BackendStartup (port=0x8240548) at postmaster.c:1924 #19 0x08100bcd in ServerLoop () at postmaster.c:1009 #20 0x0810078e in PostmasterMain (argc=1, argv=0x8227468) at postmaster.c:788 #21 0x080dee2b in main (argc=1, argv=0xb644) at main.c:210 #22 0x42017589 in __libc_start_main () from /lib/i686/libc.so.6 (gdb) quit The program is running. Quit anyway (and detach it)? (y or n) y Detaching from program: /xxx/bin.Linux/postgres, process 23663 --- Tom Lane <[EMAIL PROTECTED]> wrote: > Litao Wu <[EMAIL PROTECTED]> writes: > > One difference between these two databases > > is the one having REINDEX problem is
Re: [PERFORM] Query performance
> Can I get any better performance? You can try bumping your sort memory way up (for this query only). Another method would be to cluster the table by the symbol column (eliminates the expensive sort). If you could run a very simple calculation against open & close numbers to eliminate a majority of symbols early, that would be useful as well. ---(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
Re: [PERFORM] Query performance
Thanks this query works for what I want. So here is an output of the explain analyze: QUERY PLAN Limit (cost=2421582.59..2421582.65 rows=25 width=29) (actual time=1985800.32..1985800.44 rows=25 loops=1) -> Sort (cost=2421582.59..2424251.12 rows=1067414 width=29) (actual time=1985800.31..1985800.35 rows=26 loops=1) Sort Key: avg(((open - "close") / (open + 1::numeric))) -> Aggregate (cost=2200163.04..2280219.09 rows=1067414 width=29) (actual time=910291.94..1984972.93 rows=22362 loops=1) -> Group (cost=2200163.04..2253533.74 rows=10674140 width=29) (actual time=910085.96..1105064.28 rows=10674140 loops=1) -> Sort (cost=2200163.04..2226848.39 rows=10674140 width=29) (actual time=910085.93..988909.94 rows=10674140 loops=1) Sort Key: symbol -> Seq Scan on oclh (cost=0.00..228404.40 rows=10674140 width=29) (actual time=20.00..137720.61 rows=10674140 loops=1) Total runtime: 1986748.44 msec (9 rows) Can I get any better performance? Thanks. -Original Message- From: Bruno Wolff III [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 2:52 PM To: Bill Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Query performance On Tue, Jun 29, 2004 at 12:33:51 -0500, Bill <[EMAIL PROTECTED]> wrote: > Ok, thanks. So let me explain the query number 2 as this is the more > difficult to write. So I have a list of stocks, this table contains the > price of all of the stocks at the open and close date. Ok, now we have a > ratio from query (1) that returns at least a very rough index of the daily > performance of a given stock, with each ratio representing the stock's > performance in one day. Now we need to average this with the same stock's > ratio every day, to get a total average for each stock contained in the > database. Now I would simply like to find a ratio like this that represents > the average of every stock in the table and simply find the greatest ratio. > Sorry about the lousy explanation before, is this a bit better? You can do something like: SELECT symbol, avg((open-close)/open) GROUP BY symbol ORDER BY avg((open-close)/open) DESC LIMIT 1; If you aren't interested in the variance of the daily change, it seems like you would be best off using the opening price for the first day you have recorded for the stock and the closing price on the last day and looking at the relative change. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] postgres 7.4 at 100%
I see - thanks very much. I created an index for column 'oid' which I was using in a WHERE. So rule of thumb- create an index for column(s) which I use in WHERE queries. So to speak. They can also sometimes assist in sorting. The OID column is special. I suggest adding a unique index to that column. In postgresql it is _possible_ for the oid counter to wraparound, hence if you rely on oids (not necessarily a good idea), it's best to put a unique index on the oid column. I _strongly_ suggest that you read this: http://www.postgresql.org/docs/7.4/static/indexes.html Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] postgres 7.4 at 100%
I see - thanks very much. I created an index for column 'oid' which I was using in a WHERE. So rule of thumb- create an index for column(s) which I use in WHERE queries. Thanks, Chis On Wed, 30 Jun 2004 15:30:52 +0800, Christopher Kings-Lynne <[EMAIL PROTECTED]> wrote: > > > > Why would I or would I not create multiple indexes in a table? I > > created another index in the same table an it's improved performance > > even more. > > You create indexes when you need indexes. Indexes are most helpful when > they match the WHERE clause of your selects. > > So, if you commonly do one query that selects on one column, and another > query that selects on two other columns - then create one index on the > first column and another index over the second two columns. > > Chris > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] postgres 7.4 at 100%
Why would I or would I not create multiple indexes in a table? I created another index in the same table an it's improved performance even more. You create indexes when you need indexes. Indexes are most helpful when they match the WHERE clause of your selects. So, if you commonly do one query that selects on one column, and another query that selects on two other columns - then create one index on the first column and another index over the second two columns. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] postgres 7.4 at 100%
Oh my, creating an index has absolutely reduced the times it takes to query from around 700 ms to less than 1 ms! Thanks so much for all your help. You've saved me! One question: Why would I or would I not create multiple indexes in a table? I created another index in the same table an it's improved performance even more. Thanks, Chris On Tue, 29 Jun 2004 09:03:24 -0700, Gavin M. Roy <[EMAIL PROTECTED]> wrote: > > Is the from field nullable? If not, try "create index calllogs_from on > calllogs ( from );" and then do an explain analyze of your query. > > Gavin > > > > Chris Cheston wrote: > > >ok i just vacuumed it and it's taking slightly longer now to execute > >(only about 8 ms longer, to around 701 ms). > > > >Not using indexes for calllogs(from)... should I? The values for > >calllogs(from) are not unique (sorry if I'm misunderstanding your > >point). > > > >Thanks, > > > >Chris > > > >On Tue, 29 Jun 2004 16:21:01 +0800, Christopher Kings-Lynne > ><[EMAIL PROTECTED]> wrote: > > > > > >>>live=# explain analyze SELECT id FROM calllogs WHERE from = 'you'; > >>>QUERY PLAN > >>>-- > >>> Seq Scan on calllogs (cost=0.00..136.11 rows=24 width=4) (actual > >>>time=0.30..574.72 rows=143485 loops=1) > >>> Filter: (from = 'you'::character varying) > >>> Total runtime: 676.24 msec > >>>(3 rows) > >>> > >>> > >>Have you got an index on calllogs(from)? > >> > >>Have you vacuumed and analyzed that table recently? > >> > >>Chris > >> > >> > >> > >> > > > >---(end of broadcast)--- > >TIP 2: you can get off all lists at once with the unregister command > >(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
