[SQL] Scalar in a range (but textual not numeric)
Large table representing non-overlapping blocks: blocks(id int4, min varchar, max varchar) SELECT * FROM blocks WHERE 'ABCDE' BETWEEN min AND max; The estimator gets the wrong plan because it doesn't realise there's (at most) only one block that can match. Can't use any of the geometry related types since we've got text here not numbers. Nothing in the archives seems quite right (AFAICT). Any smart ideas? I'm happy to trade time when updating the blocks table against lookup speed. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Scalar in a range (but textual not numeric)
On Wednesday 25 February 2004 19:18, Richard Huxton wrote:
> Large table representing non-overlapping blocks:
>
> blocks(id int4, min varchar, max varchar)
>
> SELECT * FROM blocks WHERE 'ABCDE' BETWEEN min AND max;
>
> The estimator gets the wrong plan because it doesn't realise there's (at
> most) only one block that can match.
Well, replying to myself (just one of my many bad habits) the best I've come
up with so far is to add another column with a trimmed string and do a direct
comparison against that too:
SELECT * FROM blocks WHERE substring('ABCDE',1,3)=block_segment AND 'ABCDE'
BETWEEN min AND max
This gives the planner something to work with, and on 7.4 it even renders it
down to 'ABC' first too (nice :-)
That's not quite the same though, because it means I need to split
ABCAA..ABDBB into ABCAA..ABCZZ and ABDAA..ABDZZ but it's close enough unless
someone is feeling clever this evening.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Scalar in a range (but textual not numeric)
Richard Huxton wrote: That's not quite the same though, because it means I need to split ABCAA..ABDBB into ABCAA..ABCZZ and ABDAA..ABDZZ but it's close enough unless someone is feeling clever this evening. Would (a series of) partial indexes help? Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Scalar in a range (but textual not numeric)
On Wednesday 25 February 2004 20:56, Joe Conway wrote: > Richard Huxton wrote: > > That's not quite the same though, because it means I need to split > > ABCAA..ABDBB into ABCAA..ABCZZ and ABDAA..ABDZZ but it's close enough > > unless someone is feeling clever this evening. > > Would (a series of) partial indexes help? Not here - it's the irritation of splitting my single range into two at some arbitrary boundary that bugs me with this solution. Ah well - if it was that straightforward, I wouldn't be able to charge for it. Thanks Joe -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Scalar in a range (but textual not numeric)
On Wednesday 25 February 2004 21:32, Tom Lane wrote:
> Richard Huxton <[EMAIL PROTECTED]> writes:
> > Large table representing non-overlapping blocks:
> > blocks(id int4, min varchar, max varchar)
> >
> > SELECT * FROM blocks WHERE 'ABCDE' BETWEEN min AND max;
> >
> > The estimator gets the wrong plan because it doesn't realise there's
> > (at most) only one block that can match.
>
> Even if it did realize that, it couldn't do much, because this query
> isn't indexable as it stands.
Well, it is in the sense that an index can be used. Here I'd defined pkey as
(min,max,id) and set enable_seqscan=off
-> Index Scan using prnblock_range_pkey on prnblock_range
(cost=0.00..1403.99 rows=892 width=33) (actual time=23.88..24.07 rows=1
loops=1)
Index Cond: (('09050091234'::character varying >= pr_min) AND
('09050091234'::character varying <= pr_max))
Of course, what I really want is a "varchar_range" type with its own
indexing...
> I wonder whether you could adapt the "line segment" datatype
> (see contrib/seg/) into a sort of "text segment" thingy and use the
> GiST indexing support on that. You'd have a query like
> WHERE min_max_object overlaps-operator 'ABCDE'
> and the overlaps operator would be a GiST-indexable one.
Yep, that's the sort of thing I was wanting, just not worth the trouble in
this case. It's not the heart of the system, only a corner case.
Thanks anyway Tom
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Scalar in a range (but textual not numeric)
I wrote: > try writing > WHERE 'ABCDE' >= pr_min AND 'ABCDE' <= pr_max > AND pr_min < (SELECT pr_min FROM table > WHERE pr_min > 'ABCDE' > ORDER BY pr_min LIMIT 1) > The idea here is to add an upper bound on pr_min to the index scan > conditions, so that the scan can stop short of the end of the index. Argh, got that backwards. What you are missing is a *lower* bound on pr_min, and the index scan will therefore run from the start of the index up to pr_min = 'ABCDE'. So reverse the sense of the added test: AND pr_min >= (SELECT pr_min FROM table WHERE pr_min <= 'ABCDE' ORDER BY pr_min DESC LIMIT 1) 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: [SQL] Last day of month
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > How to find the last sunday/mon/sat of any given month. There is probably a smoother way to do it, but here is a quick little function to do what you ask. Feed it a date and a number, where 0 is Sunday, 1 is Monday, etc. CREATE OR REPLACE FUNCTION lastday(date,int) RETURNS DATE AS ' DECLARE match date; tomorrow date; BEGIN SELECT TO_DATE( (SELECT EXTRACT(\'year\' FROM $1) || \' \' || EXTRACT(\'month\' FROM $1) || \' 01\'), \' MM DD\') INTO tomorrow; LOOP tomorrow := tomorrow + \'24 hours\'::interval; IF (1 = EXTRACT(\'day\' FROM tomorrow) ) THEN RETURN match; END IF; IF ($2 = EXTRACT(\'dow\' FROM tomorrow)) THEN match := tomorrow; END IF; END LOOP; END; ' LANGUAGE plpgsql; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200402252206 -BEGIN PGP SIGNATURE- iD8DBQFAPWL1vJuQZxSWSsgRAu0tAKDO7oKbxOmfDpCUYpeDSwCwyALs7QCgvKT3 x+aqhBqzm9F87ESbsMe6HdQ= =AriI -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] updating remote database
i have a database on a local machine, and another on a remote machine. I have a dialup connection between the two - so band width is a problem. What is the most efficient way of updating the remote from the local? Does SQL or postgres have simple commands for this? -- regards kg -- http://www.ootygolfclub.org poor man's tally: http://avsap.sourceforge.net ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Last day of month
Greg Sabino Mullane wrote:
How to find the last sunday/mon/sat of any given month.
There is probably a smoother way to do it, but here is a
quick little function to do what you ask. Feed it a date
and a number, where 0 is Sunday, 1 is Monday, etc.
oops...forget my last reply...I was a bit too quick on the draw. Try
this instead:
regression=# select date_trunc('month', current_date + '1
month'::interval) - '1 day'::interval;
?column?
-
2004-02-29 00:00:00
(1 row)
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [SQL] Materialized View Summary
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm not sure if my original reply made it through. Ignore the last one if it did. On Tuesday 24 February 2004 1:48 pm, Robert Treat wrote: > On Tue, 2004-02-24 at 12:11, Richard Huxton wrote: > > On Tuesday 24 February 2004 16:11, Jonathan M. Gardner wrote: > > > I've written a summary of my findings on implementing and using > > > materialized views in PostgreSQL. I've already deployed eagerly > > > updating materialized views on several views in a production > > > environment for a company called RedWeek: http://redweek.com/. As a > > > result, some queries that were taking longer than 30 seconds to run > > > now run in a fraction of a millisecond. > > > > > > You can view my summary at > > > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.h > > >tml > > have you done much concurrency testing on your snapshot views? I > implemented a similar scheme in one of my databases but found problems > when I had concurrent "refresh attempts". I ended up serializing the > calls view LOCKing, which was ok for my needs, but I thought > potentially problematic in other cases. > We are running into some small problems with deadlocks and multiple inserts. It's not a problem unless we do a mass update to the data or something like that. I'm interested in how you solved your problem. I am playing with an exclusive lock scheme that will lock all the materialized views with an exclusive lock (see Section 12.3 for a reminder on what exactly this means). The locks have to occur in order, so I use a recursive function to traverse a dependency tree to the root and then lock from there. Right now, we only have one materialized view tree, but I can see some schemas having multiple seperate trees with multiple roots. So I put in an ordering to lock the tables in a pre-defined order. But if the two dependency trees are totally seperate, it is possible for one transaction to lock tree A and then tree B, and for another to lock tree B and then tree A, causing deadlock. Unfortunately, I can't force any update to the underlying tables to force this locking function to be called. So we will probably call this manually before we touch any of those tables. In the future, it would be nice to have a hook into the locking mechanism so any kind of lock on the underlying tables can trigger this. Also, building the dependency trees is completely manual. Until I can get some functions to actually assemble the triggers and such, automatic building of the trees will be difficult. - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAPFqRqp6r/MVGlwwRAnvPAJ90lEEyaBzAfUoLZU93ZDvkojaAwwCdGjaA YBlO57OiZidZuQ5/S0u6wXM= =bMYE -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [HACKERS] Materialized View Summary
Jonathan M. Gardner wrote: You can view my summary at http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Comments and suggestions are definitely welcome. Fantastic, I was planning on a bit of materialized view investigations myself when time permits, I'm pleased to see you've started the ball rolling. I was thinking about your problem with mutable functions used in a materialized view. How about eliminating the mutable functions as much as possible from the underlying view definition, and create another view on top of the materialized view that has the mutable bits! Giving you the best of both worlds. I haven't tried this or thought it through very much - too busy - but I'd thought I'd throw it in for a bit o' head scratching, and chin stroking :) Cheers -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] [HACKERS] [SQL] Materialized View Summary
On Wed, 2004-02-25 at 03:19, Jonathan M. Gardner wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > I'm not sure if my original reply made it through. Ignore the last one if > it did. But I liked the last one :-) > > On Tuesday 24 February 2004 1:48 pm, Robert Treat wrote: > > On Tue, 2004-02-24 at 12:11, Richard Huxton wrote: > > > On Tuesday 24 February 2004 16:11, Jonathan M. Gardner wrote: > > > > I've written a summary of my findings on implementing and using > > > > materialized views in PostgreSQL. I've already deployed eagerly > > > > updating materialized views on several views in a production > > > > environment for a company called RedWeek: http://redweek.com/. As a > > > > result, some queries that were taking longer than 30 seconds to run > > > > now run in a fraction of a millisecond. > > > > > > > > You can view my summary at > > > > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.h > > > >tml > > > > have you done much concurrency testing on your snapshot views? I > > implemented a similar scheme in one of my databases but found problems > > when I had concurrent "refresh attempts". I ended up serializing the > > calls view LOCKing, which was ok for my needs, but I thought > > potentially problematic in other cases. > > > > We are running into some small problems with deadlocks and multiple > inserts. It's not a problem unless we do a mass update to the data or > something like that. I'm interested in how you solved your problem. > Well, I have two different cases actually. In one case I have a master table with what are essentially 4 or 5 matviews based off of that. I don't allow updates to the matviews, only to the master table, and only via stored procedures. This would work better if locking semantics inside of pl functions worked properly, but currently we have the application lock the table in exclusive access mode and then call the function to make the data changes which then fires off a function to update the matviews. Since it's all within a transaction, readers of the matviews are oblivious to the change. IMO this whole method is a wizardry in database hack jobs that I would love to replace. The second case, and this one being much simpler, started out as a view that does aggregation across several other views and tables, which is pretty resource intensive but only returns 4 rows. I refresh the matview via a cron job which basically does a SELECT * FOR UPDATE on the matview, deletes the entire contents, then does an INSERT INTO matview SELECT * FROM view. Again since it's in a transaction, readers of the matview are happy (and apps are only granted select on the matview). Concurrency is kept because the cron job must wait to get a LOCK on the table before it can proceed with the delete/update. I have a feeling that this method could fall over given a high enough number of concurrent updaters, but works pretty well for our needs. > I am playing with an exclusive lock scheme that will lock all the > materialized views with an exclusive lock (see Section 12.3 for a > reminder on what exactly this means). The locks have to occur in order, > so I use a recursive function to traverse a dependency tree to the root > and then lock from there. Right now, we only have one materialized view > tree, but I can see some schemas having multiple seperate trees with > multiple roots. So I put in an ordering to lock the tables in a > pre-defined order. > > But if the two dependency trees are totally seperate, it is possible for > one transaction to lock tree A and then tree B, and for another to lock > tree B and then tree A, causing deadlock. > > Unfortunately, I can't force any update to the underlying tables to force > this locking function to be called. So we will probably call this > manually before we touch any of those tables. Yeah, I ran into similar problems as this, but ISTM you could do a before update trigger on the matview to do the locking (though I'd guess this would end in trouble due to plpgsql lock semantics, so maybe i shouldn't send you down a troubled road...) > > In the future, it would be nice to have a hook into the locking mechanism > so any kind of lock on the underlying tables can trigger this. > > Also, building the dependency trees is completely manual. Until I can get > some functions to actually assemble the triggers and such, automatic > building of the trees will be difficult. > I just noticed that your summary doesn't make use of postgresql RULES in any way, how much have you traveled down that path? We had cooked up a scheme for our second case where we would have a table that held an entry for the matview and then a timestamp of the last update/insert into any of the base tables the matview depended on. when then would create rules on all the base tables to do an update to the refresh table any time they were updated/inserted/deleted. We would then put a corresponding rule on the matview so
