Re: [PERFORM] date - range
On Sat, Apr 02, 2005 at 00:01:31 -0700,
Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Fri, Apr 01, 2005 at 09:59:44PM -0800, Mischa wrote:
> > >
> > > select ... where first_date <= today and last_date >= today
> > >
> > > Whatever index we create system always does a sequential scan (which I can
> > > understand). Has someone a smarter solution?
> >
> > Yep, standard SQL problem. The answer is sort of a hand-rolled GIST index.
>
> That might not be necessary in this case.
Even though you get an index scan, I don't think it is going to be
very fast as there are going to be a lot of entries with first_date
<= current_date. If the requests are almost always for the current date,
then switching the order of columns in the index will help, since there
will probably be few orders for future service, so that the current
date being <= the last_date will be a much better indicator of whether
they have service. If requests are made well into the past then this
approach will have the same problem as checking first_date first.
He will probably get faster lookups using rtree or gist indexes as
he really is checking for containment.
>
> CREATE TABLE foo (
> id serial PRIMARY KEY,
> first_date date NOT NULL,
> last_date date NOT NULL,
> CONSTRAINT check_date CHECK (last_date >= first_date)
> );
>
> /* populate table */
>
> CREATE INDEX foo_date_idx ON foo (first_date, last_date);
> ANALYZE foo;
>
> EXPLAIN SELECT * FROM foo
> WHERE first_date <= current_date AND last_date >= current_date;
> QUERY PLAN
>
>
> Index Scan using foo_date_idx on foo (cost=0.01..15.55 rows=97 width=12)
>Index Cond: ((first_date <= ('now'::text)::date) AND (last_date >=
> ('now'::text)::date))
> (2 rows)
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Follow-Up: How to improve db performance with $7K?
Vivek Khera wrote: On Mar 31, 2005, at 9:01 PM, Steve Poe wrote: Now, we need to purchase a good U320 RAID card now. Any suggestions for those which run well under Linux? Not sure if it works with linux, but under FreeBSD 5, the LSI MegaRAID cards are well supported. You should be able to pick up a 320-2X with 128Mb battery backed cache for about $1k. Wicked fast... I'm suprized you didn't go for the 15k RPM drives for a small extra cost. Wow, okay, so I'm not sure where everyone's email went, but I got over a weeks worth of list emails at once. Several of you have sent me requests on where we purchased our systems at. Compsource was the vendor, www.c-source.com or www.compsource.com.The sales rep we have is Steve Taylor or you can talk to the sales manager Tom.I've bought hardware from them for the last 2 years and I've been very pleased. I'm sorry wasn't able to respond sooner. Steve, The LSI MegaRAID cards are where its at. I've had -great- luck with them over the years. There were a few weird problems with a series awhile back where the linux driver needed tweaked by the developers along with a new bios update. The 320 series is just as Vivek said, wicked fast. Very strong cards. Be sure though when you order it to specificy the battery backup either with it, or make sure you buy the right one for it. There are a couple of options with battery cache on the cards that can trip you up. Good luck on your systems! Now that I've got my email problems resolved I'm definitely more than help to give any information you all need. ---(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] Query Optimizer Failure / Possible Bug
Thank you very much for your reply. I'll try to modify it. Josh Berkus wrote: Hannes, The query and the corresponding EXPLAIN is at http://hannes.imos.net/query.txt The problem is that you're using a complex corellated sub-select in the SELECT clause: SELECT d.delivery_id, da.article_no, da.amount, ( SELECT COUNT(*) FROM serials s INNER JOIN rma_ticket_serials rts ON ( s.serial_id = rts.serial_id ) WHERE s.article_no = da.article_no AND s.delivery_id = d.delivery_id AND rts.replace = FALSE ) AS replaced_serials This means that the planner pretty much has to iterate over the subquery, running it once for each row in the result set. If you want the optimizer to use a JOIN structure instead, put the subselect in the FROM clause. -- imos Gesellschaft fuer Internet-Marketing und Online-Services mbH Alfons-Feifel-Str. 9 // D-73037 Goeppingen // Stauferpark Ost Tel: 07161 93339-14 // Fax: 07161 93339-99 // Internet: www.imos.net ---(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] Delete query takes exorbitant amount of time
> Each value has 1/13th of the table, which is too many rows per value to > make an IndexScan an efficient way of deleting rows from the table. But, the original question was that the delete that was taking a long time was on a different table. I tried to delete 150 rows from a table with 750 rows, which is FK referenced from this large table. If I understand correctly, Tom suggested that the length of time was due to a sequential scan being done on the large table for each value being deleted from the small one. (I have no formal training in database administration nor database theory, so please excuse me if I am being dumb.) For this FK check, there only need be one referring id to invalidate the delete. ISTM that for any delete with a FK reference, the index could always be used to search for a single value in the referring table (excepting very small tables). Why then must a sequential scan be performed in this case, and/or in general? -- Karim Nassar Department of Computer Science Box 15600, College of Engineering and Natural Sciences Northern Arizona University, Flagstaff, Arizona 86011 Office: (928) 523-5868 -=- Mobile: (928) 699-9221 ---(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] Delete query takes exorbitant amount of time
> Well, based upon the evidence so far, the Optimizer got it right: Agreed. So, this means that the answer to my original question is "that delete gonna take a long time"? Seems that there is still something wrong. From what I can tell from everyones questions, the FK constraint on measurement is causing multiple seq scans for each value deleted from int_sensor_meas_type. However, when deleting a single value, the FK check should use the index, so my ~190 deletes *should* be fast, no? > IndexScan, value=1elapsed= 29ms cost=883881 190 * 29ms is much less than 40 minutes. What am I missing here? > Karim, > Please do: > > select id_int_sensor_meas_type, count(*) > from measurement > group by id_int_sensor_meas_type > order by count(*) desc; id_int_sensor_meas_type | count -+ 31 | 509478 30 | 509478 206 | 509478 205 | 509478 204 | 509478 40 | 509478 39 | 509478 197 | 509478 35 | 509478 34 | 509478 33 | 509478 32 | 509478 41 | 509477 This sample dataset has 13 measurements from a weather station over 3 years, hence the even distribution. Continued thanks, -- Karim Nassar Department of Computer Science Box 15600, College of Engineering and Natural Sciences Northern Arizona University, Flagstaff, Arizona 86011 Office: (928) 523-5868 -=- Mobile: (928) 699-9221 ---(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] Delete query takes exorbitant amount of time
On Mon, 28 Mar 2005, Stephan Szabo wrote: > > On Mon, 28 Mar 2005, Simon Riggs wrote: > > > run the EXPLAIN after doing > > > SET enable_seqscan = off ... > I think you have to prepare with enable_seqscan=off, because it > effects how the query is planned and prepared. orfs=# SET enable_seqscan = off; SET orfs=# PREPARE test2(int) AS SELECT 1 from measurement where orfs-# id_int_sensor_meas_type = $1 FOR UPDATE; PREPARE orfs=# EXPLAIN ANALYZE EXECUTE TEST2(1); -- non-existent QUERY PLAN - Index Scan using measurement__id_int_sensor_meas_type_idx on measurement (cost=0.00..883881.49 rows=509478 width=6) (actual time=29.207..29.207 rows=0 loops=1) Index Cond: (id_int_sensor_meas_type = $1) Total runtime: 29.277 ms (3 rows) orfs=# EXPLAIN ANALYZE EXECUTE TEST2(197); -- existing value QUERY PLAN - Index Scan using measurement__id_int_sensor_meas_type_idx on measurement (cost=0.00..883881.49 rows=509478 width=6) (actual time=12.903..37478.167 rows=509478 loops=1) Index Cond: (id_int_sensor_meas_type = $1) Total runtime: 38113.338 ms (3 rows) -- Karim Nassar Department of Computer Science Box 15600, College of Engineering and Natural Sciences Northern Arizona University, Flagstaff, Arizona 86011 Office: (928) 523-5868 -=- Mobile: (928) 699-9221 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Query Optimizer Failure / Possible Bug
hm, a few days and not a single reply :|
any more information needed? test data? simplified test case? anything?
thanks
Hannes Dorbath wrote:
The query and the corresponding EXPLAIN is at
http://hannes.imos.net/query.txt
I'd like to use the column q.replaced_serials for multiple calculations
in the SELECT clause, but every time it is referenced there in some way
the whole query in the FROM clause returning q is executed again.
This doesn't make sense to me at all and eats performance.
If this wasn't clear enough, for every
q.replaced_serials AS some_column
in the SELECT clause there is new block of
---
-> Aggregate (cost=884.23..884.23 rows=1 width=0)
-> Nested Loop (cost=0.00..884.23 rows=1 width=0)
-> Index Scan using ix_rma_ticket_serials_replace on
rma_ticket_serials rts (cost=0.00..122.35
rows=190 width=4)
Index Cond: ("replace" = false)
-> Index Scan using pk_serials on serials s
(cost=0.00..3.51 rows=1 width=4)
Index Cond: (s.serial_id = "outer".serial_id)
Filter: ((article_no = $0) AND (delivery_id = $1))
---
in the EXPLAIN result.
For those who wonder why I do this FROM (SELECT...). I was searching for
a way to use the result of an subselect for multiple calculations in the
SELECT clause and return that calculation results as individual columns.
I tested a bit further and found out that PG behaves the same in case q
is a view. This makes me wonder how efficient the optimizer can work
with views - or even worse - nested views.
Tested and reproduced on PG 7.4.1 linux and 8.0.0 win32.
Thanks in advance,
Hannes Dorbath
--
imos Gesellschaft fuer Internet-Marketing und Online-Services mbH
Alfons-Feifel-Str. 9 // D-73037 Goeppingen // Stauferpark Ost
Tel: 07161 93339-14 // Fax: 07161 93339-99 // Internet: www.imos.net
---(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
[PERFORM] coalesce alternative
I have used coalesce function for null fields but coalesce is too slow. I need fast alternative for coalesce ALÝ ÇELÝK ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] How to improve db performance with $7K?
Yeah, 35Mb per sec is slow for a raid controller, the 3ware mirrored is about 50Mb/sec, and striped is about 100 Dave PFC wrote: With hardware tuning, I am sure we can do better than 35Mb per sec. Also WTF ? My Laptop does 19 MB/s (reading <10 KB files, reiser4) ! A recent desktop 7200rpm IDE drive # hdparm -t /dev/hdc1 /dev/hdc1: Timing buffered disk reads: 148 MB in 3.02 seconds = 49.01 MB/sec # ll "DragonBall 001.avi" -r--r--r--1 peufeu users218M mar 9 20:07 DragonBall 001.avi # time cat "DragonBall 001.avi" >/dev/null real0m4.162s user0m0.020s sys 0m0.510s (the file was not in the cache) => about 52 MB/s (reiser3.6) So, you have a problem with your hardware... ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
