In SQL, given a table T, with two fields f1, f2,
is it possible to create an index such that the same record is indexed in the
index, once with field f1 and once with field f2. (I am not looking for a
compound index in which the key would look like , instead there should
be two entries in the
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
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 such as "where 'a string expected to be
of size range 100 to 500' = tabelWithStrings.stringSearc
Hi,
I realise this isn't strictly an SQL question, but I figured this list
might be better suited than the general one. Please let me know if not.
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
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.
Hi,
I realise this isn't strictly an SQL question, but I figured this list might be
better suited than the general one. Please let me know if not.
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 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
I have two tables:
TABLE ITEM
(
ITEM_PK serial,
RETAIL_PRICE numeric (7,2) NOT NULL,
...
PRIMARY KEY (ITEM_PK)
)
TABLE SERIAL_NO
(
SERIAL_NO_PK serial,
NO varchar (20) NOT NULL,
NAME varchar (20),
ITEM_FK integer NOT NULL,
PRIMARY KEY (SERIAL_NO_PK)
);
common query:
SELECT ITEM.ITEM_PK FROM IT
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
; To: pgsql-sql@postgresql.org
> Subject: [SQL] Indexing an array?
>
>
> Hi,
>
> I have a problem with arrays in Postgres. I want to create a
> really large array, lets say 3 billion characters long.
>
> As far I could read from the documentation - this should be
> pos
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
Hi,
I have a problem with arrays in Postgres. I want to create a really
large array, lets say 3 billion characters long.
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.
Lets say, I want to get element 2,675,345,328.
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
Is it a good idea to index timestamp fields? what about date fields in
general?
thanks
---(end of broadcast)---
TIP 8: explain analyze is your friend
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
Hi all,
I've been doing a little reading on indexing in
prelude to indexing my db.
I have the following to ask:
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 i
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-
>
>
Hi-
This is more just trying to understand what is going on under the hood of
pgsql. I have read through the archives that there is no difference between
index on char, varchar or text. I am wondering why? I understand all the
arguments about saving space but I am specifically asking about ind
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
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;
This works fine as a view, since I have made the id's unique be
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
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 join
will produce a substancial amount of data to be filtere
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
---
Folks,
Q: Are brand-new rows included in PostgreSQL indexed immediately?
Detail: I have several "keyset holding" tables where lists of primary
key ids are inserted for immediate retrieval by the user. After a few
retrievals, these keysets are not used again. The purpose of this
arrangement is
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
We make cartoons here.
But let's say we were working on 3 different Cop movies.
Our projects are devided into project, sequence, and shot.
project sequence
shot
CopFilm1 alley shooting death of the bad
guy
CopFilm2 car chase
death of the bad guy
CopFilm3 car chase
[ 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
Hi there, I am having some difficulties with using arrays in pgaccess
relating to arrays.
Here is an example schema:
CREATE TABLE arraykeys (
akID int,
akName varchar(12),
PRIMARY KEY(akID)
);
CREATE TABLE items (
itID serial,
itProperties bool[],
PRIMARY KEY(itID)
);
61 matches
Mail list logo
