No problem, sir, hopefully I could help. I failed to mention that I've
discovered some bugs in the PostgreSQL 8.4 XML implementation that forced me
to take pause and ultimately forego XML with Postgres. I haven't looked at 9
yet, but considering the current lack of interest and/or disdain so many
h
Hi,
While hashing is certainly a good idea, you really should consider some
issues well before you get to that point. Trust me, this could save you some
headaches. First, though you're probably already aware, two XML documents
can be the same document, but with very different literal representatio
You can use a hash index for this. It's drawback is that it is not
yet WAL enabled and if your DB crashes you will need to rebuild the
index to fix the corruption. It works well(only) with equality
searches. If it is a scenario where you must have WAL, use a
function index based on the hash of the
If the performance against an index doesn't cut it, we would be forced
to choose just such an implementation, but if pg can do it straight up
that would be less work for us. A good thing, to be sure.
On 11/30/2010 10:50 AM, jose wrote:
> Why don't you use some type of hash like md5 for indexing ?
Why don't you use some type of hash like md5 for indexing ?
2010/11/30 Rob Sargent :
> Were we to create a table which included a text field for a small block
> of xml (100-1000 chars worth), would an index on that field be useful
> against exact match queries?
>
> We're wondering if a criterion s
David Cottingham <[EMAIL PROTECTED]> writes:
> I have a table containing a field named location, of type point, i.e. a
> position in two dimensions. The table has several million records in, and I
> need to extract those records whose location value is contained within a
> certain bounding box.
I am new to PostgreSQL but isn't this query the same as doing an INNER
JOIN?
For a true LEFT JOIN should it not be as follows?
SELECT ITEM.ITEM_PK
FROM ITEM
LEFT JOIN SERIAL_NO ON SERIAL_NO.ITEM_FK = ITEM.ITEM_PK
AND SERIAL_NO.NO ='WX1234'
GROUP BY ITEM.ITEM_PK
Using an AND instead of WHERE fo
Rod Taylor wrote:
Sequential despite the indices? Or is this because the tables of my test
DB are virtually empty?
This is it. PostgreSQL changes strategies with data load. Performance
testing must be done on an approximation of the real data (both values
and size).
Thanks for your responses
Milorad Poluga wrote:
Try to execute this modification of your query :
SELECT ITEM.ITEM_PK FROM ITEM
LEFT JOIN SERIAL_NO
ON ( SERIAL_NO.ITEM_FK = ITEM.ITEM_PK
AND SERIAL_NO.NO ='WX1234' )
GROUP BY ITEM.ITEM_PK
SELECT ITEM.ITEM_PK FROM ITEM
LEFT JOIN SERIAL_NO O
T E Schmitz wrote:
Sequential despite the indices? Or is this because the tables of my test
DB are virtually empty?
Yes - read up on analyse and column statistics for details. Oh, you've
probably missed about vacuuming too.
--
Richard Huxton
Archonet Ltd
---(en
> Sequential despite the indices? Or is this because the tables of my test
> DB are virtually empty?
This is it. PostgreSQL changes strategies with data load. Performance
testing must be done on an approximation of the real data (both values
and size).
--
---(end of br
Silke Trissl wrote:
As far I could read from the documentation - this should be possible.
But my question is, is there a kind of index on the array.
If your needs are a bit more modest (say, a few thousands instead of
billions) the stuff in contrib/intarray works well; and if you
needed types
Silke Trissl <[EMAIL PROTECTED]> writes:
> I have a problem with arrays in Postgres. I want to create a really
> large array, lets say 3 billion characters long.
Forget it --- quite aside from indexing inefficiencies, the max size of
an array (or any other single field) is just 1Gb. Don't try to
Really seems like that array should be a separate table, then Postgres would
definitely know how to index it.
Dmitri
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Silke Trissl
> Sent: Thursday, September 08, 2005 12:14 PM
> To: pgsql-sql@postgre
Silke,
> I have a problem with arrays in Postgres. I want to create a really
> large array, lets say 3 billion characters long.
Change your application design.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 2
On Wed, Oct 15, 2003 at 21:25:17 +0100,
teknokrat <[EMAIL PROTECTED]> wrote:
>
> I have a lot of queries of the " where timestamp < some date " type and
> was wondering if an index would improve performance. None of the
> timestamps are null and they are always populated
Unless that query ret
Christopher Browne wrote:
teknokrat <[EMAIL PROTECTED]> writes:
Is it a good idea to index timestamp fields? what about date fields in
general?
If you need to order by a timestamp, then it can be worthwhile.
If that timestamp can be null, and is rarely populated, then you might
get a _big_ ben
teknokrat <[EMAIL PROTECTED]> writes:
> Christopher Browne wrote:
>
>> teknokrat <[EMAIL PROTECTED]> writes:
>>
>>>Is it a good idea to index timestamp fields? what about date fields in
>>>general?
>> If you need to order by a timestamp, then it can be worthwhile.
>> If that timestamp can be null,
teknokrat <[EMAIL PROTECTED]> writes:
> Is it a good idea to index timestamp fields? what about date fields in
> general?
If you need to order by a timestamp, then it can be worthwhile.
If that timestamp can be null, and is rarely populated, then you might
get a _big_ benefit from creating a part
James,
> if I had three tables for a many to many relationship say A, B, AND C
> B being the lookup. B being a huge 50k rows plus column and made just two
forigen keys(b.a_id,b.c_id).
> is it best to create two non-unique indexes or one unique index on both
fields?
That depends on whether all t
d; 'Bruce Momjian'
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] indexing on char vs varchar
>
>
> Beth,
>
> Oh, and you should take this sort of question to the new
> performance list:
> [EMAIL PROTECTED]
>
> --
> -Josh Berkus
> Aglio Database
Beth,
Oh, and you should take this sort of question to the new performance list:
[EMAIL PROTECTED]
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Beth,
> SorryI don't understand. The length is at the front of what?
In some RDBMSs, the VARCHAR data type has a 2 or 4-byte indicator of the
length of the stored string before the data itself, while CHAR does not
require this information because it is fixed-length. This makes the CHAR
SorryI don't understand. The length is at the front of what?
-Beth
> -Original Message-
> From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, October 02, 2002 10:06 AM
> To: Beth Gatewood
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] inde
We store all the text/char/varchar types with the length at the front so
we don't have such optimizations. We do have "char", in quotes, which
is a single character, but that's about it.
---
Beth Gatewood wrote:
> Hi-
>
>
Bruno,
> My suggestion:
> SELECT t3.id, coalesce(t1.name, t2.name), t1.abbreviation,
> coalesce(t1.juris_id, t2.juris_id) from
> (t3 left join t1 using (id)) left join t2 using (id);
Cool! I didn't think of that. I'll give it a try.
-Josh
---(end of broadcast)
Just in case there was some misunderstanding of my suggestion here is
what I had in mind.
Your query:
SELECT t1.id, t1.name, t1.abbreviation, t1.juris_id
FROM t1
UNION ALL
SELECT t2.id, t2.name, NULL, t2.juris_id
FROM t2;
My suggestion:
SELECT t3.id, coalesce(t1.name, t2.name), t1.abbreviation,
Bruno,
> I think you might be able to do this using (one sided) outer joins of the
event
> table to the Case and Trial Group tables. The join rules will need to work
for
> exactly one of the two tables. You probably will want to use case statements
in
> the select list to pick values from the
On Tue, Jul 16, 2002 at 15:42:23 -0700,
Josh Berkus <[EMAIL PROTECTED]> wrote:
>
> Table "events", the largest table in the database, contains event schedule
> listing with 11 attributes and one dependant table as well as recursive
> relationships between events. Each event record can be (a
On Tuesday 16 Jul 2002 11:42 pm, Josh Berkus wrote:
> OK, I guess I'll have to get into detail:
>
[detail on cases and trial-groups]
>
> Thus, I need to relate (in views and queries) each Event to the Union of
> Cases and Trial Groups. I just can't figure out how to do so without the
> database
Bruno,
> This sounds like a design issue. This makes it seem like the events
> should be broken out into their own table and the other two tables
> should get joined with the events table when needed.
>
OK, I guess I'll have to get into detail:
Table "cases" is the database's third largest tab
On Tue, Jul 16, 2002 at 09:36:31 -0700,
Josh Berkus <[EMAIL PROTECTED]> wrote:
> Bruno,
>
> > It wouldn't have to be a dummy table. You could have both sets of
> > data
> > in the same table.
>
> Per my original e-mail, this is not an option.
>
> Basically, the two tables have nothing in co
Bruno,
> It wouldn't have to be a dummy table. You could have both sets of
> data
> in the same table.
Per my original e-mail, this is not an option.
Basically, the two tables have nothing in commmon *except* that events
can be scheduled against either table. Otherwise, the two tables have
On Mon, Jul 15, 2002 at 17:31:24 -0700,
Josh Berkus <[EMAIL PROTECTED]> wrote:
> Stephan,
>
> > We had a discussion recently on -general about this. Right now the
> > planner won't push the conditions down into the arms of the union because
> > noone's been sure under what conditions the optim
On Mon, 15 Jul 2002, Josh Berkus wrote:
> Stephan,
>
> > We had a discussion recently on -general about this. Right now the
> > planner won't push the conditions down into the arms of the union because
> > noone's been sure under what conditions the optimization is safe.
>
> So, if performance i
Stephan,
> We had a discussion recently on -general about this. Right now the
> planner won't push the conditions down into the arms of the union because
> noone's been sure under what conditions the optimization is safe.
So, if performance is horrible with the view, I should use a dummy table
On Mon, 15 Jul 2002, Josh Berkus wrote:
> Folks,
>
> I have two tables which are often browsed together through a UNION view, like:
>
> CREATE VIEW two_tables AS
> SELECT t1.id, t1.name, t1.abbreviation, t1.juris_id
> FROM t1
> UNION ALL
> SELECT t2.id, t2.name, NULL, t2.juris_id
> FROM t2;
>
> T
On Fri, 12 Oct 2001, Patrik Kudo wrote:
> kudo=# select version();
>version
> --
> PostgreSQL 7.1.3 on i386--freebsd4.3, compiled by GCC 2.95.3
> (1 row)
>
> kudo=# create index person_lower_lname_idx on per
Patrik Kudo <[EMAIL PROTECTED]> writes:
> Thanks for your respons, but I'm afraid it didn't help. I've succeeded
> with indexing my table using functional indices, but the problem is that
> the index I create won't work with my query.
Works for me:
test71=# create table person (last_name text);
Patrik Kudo wrote:
> [...]
>
> Is it at all possible to create an index on lower(name), and in that case,
> what type of index and using what syntax?
You'll want to look at section 7.5 "Functional Indices" in the 7.1.3 'User's Guide'.
Allan.
---(end of broadcast)---
On Thu, 11 Oct 2001, Patrik Kudo wrote:
> Hi!
>
> If I want to be able to search for stringmatches using LIKE, doing
> something like the following:
>
> select id, name from table1 where lower(name) like 'somestring%';
>
> Actually I will be joining with some other table on id too, but the joi
On Thu, Oct 11, 2001 at 02:28:34PM +0200, Patrik Kudo wrote:
> Hi!
>
> If I want to be able to search for stringmatches using LIKE, doing
> something like the following:
>
> select id, name from table1 where lower(name) like 'somestring%';
>
> Actually I will be joining with some other table on
On Tue, Oct 02, 2001 at 09:47:09AM -0700, Josh Berkus wrote:
> Folks,
>
> Q: Are brand-new rows included in PostgreSQL indexed immediately?
>
A: Yes, and that's why you don't add indices willy-nilly to all possible
column combos on any given table: INSERTs get real slow.
Ross
---
Tom Lane wrote:
>
> [ redirected to pgsql-interfaces, which seems a more appropriate venue ]
>
> David Stanaway <[EMAIL PROTECTED]> writes:
> > --.. And now the query that I am having problems with.
> >
> > SELECT itID, itProperties[akID], akName
> > FROM items, arraykeys;
> >
> > In pgaccess,
>
Josh Berkus wrote:
Dado,
Yeah, me again. What
can I say? I'm procrastinating, and list stuff
is a great way to do it. And I prefer design theory issues.
project sequence
shot
CopFilm1 alley shooting death of the bad guy
CopFilm2 car chase
death of the bad guy
Co
Dado,
Yeah, me again. What can I say? I'm procrastinating, and list stuff
is a great way to do it. And I prefer design theory issues.
project sequence shot
CopFilm1 alley shooting death of the bad guy
CopFilm2 car chase death of the bad guy
CopFilm3 car
[ redirected to pgsql-interfaces, which seems a more appropriate venue ]
David Stanaway <[EMAIL PROTECTED]> writes:
> --.. And now the query that I am having problems with.
>
> SELECT itID, itProperties[akID], akName
> FROM items, arraykeys;
>
> In pgaccess,
> when I try to execute the same query
47 matches
Mail list logo
