Re: [GENERAL] Indexing large table of coordinates with GiST

pgsql-general-ow...@postgresql.org] On Behalf Of Nathan Clayton Sent: January-15-15 19:19 To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Indexing large table of coordinates with GiST On 1/15/2015 12:36 PM, Daniel Begin wrote: > > Thank, there is a lot of potential ways to resolve t

Re: [GENERAL] Indexing large table of coordinates with GiST

pgsql-general-ow...@postgresql.org] On Behalf Of Nathan Clayton Sent: January-15-15 19:19 To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Indexing large table of coordinates with GiST On 1/15/2015 12:36 PM, Daniel Begin wrote: > > Thank, there is a lot of potential ways to resolve t

Re: [GENERAL] Indexing large table of coordinates with GiST

Please let me one more guess ^^ Third guess : you are using topology (nodes are indexed by node_id). - If this is the case, you could use postgis topology. - The gain is that with this topology model, you store *shared linestring*, and not shared points. More seriously from what you say it

Re: [GENERAL] Indexing large table of coordinates with GiST

On 1/15/2015 12:36 PM, Daniel Begin wrote: Thank, there is a lot of potential ways to resolve this problem! For Rob, here is a bit of context concerning my IT environment… Windows 7 64b Desktop, running with an Intel i7 core and 16GB ram. The PostgreSQL 9.3 database is stored on a 3TB extern

Re: [GENERAL] Indexing large table of coordinates with GiST

 On January 15, 2015 at 12:36:29 PM, Daniel Begin (jfd...@hotmail.com(mailto:jfd...@hotmail.com)) wrote: > Paul, the nodes distribution is all over the world but mainly over inhabited > areas. However, if I had to define a limit of some sort, I would use the > dateline. Concerning spatial queri

Re: [GENERAL] Indexing large table of coordinates with GiST

Thank, there is a lot of potential ways to resolve this problem! For Rob, here is a bit of context concerning my IT environment… Windows 7 64b Desktop, running with an Intel i7 core and 16GB ram. The PostgreSQL 9.3 database is stored on a 3TB external drive (USB-3 connection with write cach

Re: [GENERAL] Indexing large table of coordinates with GiST

As Remi notes, going with a pointcloud approach might be wiser, particularly if you aren’t storing much more about the points that coordinates and other lidar return information. Since you’re only working with points, depending on your spatial distribution (over poles? dateline?) you might just

Re: [GENERAL] Indexing large table of coordinates with GiST

Hey, You may want to post this on postGIS list. I take that so many rows mean either raster or point cloud. If it is point cloud simply consider using pg_pointcloud. A 6 billion point cloud is about 600 k lines for one of my data set. If it is raster, you may consider using postgis raster type. I

Re: [GENERAL] Indexing large table of coordinates with GiST

On 01/15/2015 05:44 AM, Daniel Begin wrote: Hi, I'm trying to create an index on coordinates (geography type) over a large table (4.5 billion records) using GiST... CREATE INDEX nodes_geom_idx ON nodes USING gist (geom); The command ran for 5 days until my computer stops because a power outage!

Re: [GENERAL] Indexing large table of coordinates with GiST

On 1/15/2015 6:44 AM, Daniel Begin wrote: Hi, I'm trying to create an index on coordinates (geography type) over a large table (4.5 billion records) using GiST... CREATE INDEX nodes_geom_idx ON nodes USING gist (geom); The command ran for 5 days until my computer stops because a power outage! B

Re: [GENERAL] Indexing large table of coordinates with GiST

I'd restructure the table to be split into perhaps 100 or so inherited tables (or more). That many rows in a table are usually not efficient with postgres in my experience. My target is to keep the tables under about 100 million rows. I slice them up based on the common query patterns, usually by s

Re: [GENERAL] indexing elements of a csv ?

2013/3/13 Ian Lawrence Barwick : > 2013/3/12 Gauthier, Dave : >> Hi: >> >> v9.0.1 on linux. >> >> I have a table with a column that is a csv. Users will select records based >> upon the existence of an element of the csv. There is an index on that >> column but I'm thinking that it won't be of mu

Re: [GENERAL] indexing elements of a csv ?

An option would be to create a column of type tsvector. That way you could do text searches using partial words or words and get results including those containing forms of the word. From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] o

Re: [GENERAL] indexing elements of a csv ?

2013/3/12 Gauthier, Dave : > Hi: > > v9.0.1 on linux. > > I have a table with a column that is a csv. Users will select records based > upon the existence of an element of the csv. There is an index on that > column but I'm thinking that it won't be of much use in this situation. Is > there a wa

Re: [GENERAL] Indexing JSON type

Ankur Soni wrote: > Hi, > > I am using PostgreSQL 9.2. I was wondering if anyone has been successful in > indexing JSON data? Is it possible to index all JSON data (keys and values)? http://people.planetpostgresql.org/andrew/index.php?/archives/249-Using-PLV8-to-index-JSON.html Andreas -- Re

Re: [GENERAL] Indexing question

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of amit sehas > Sent: Tuesday, August 14, 2012 12:55 PM > To: pgsql-...@postgresql.org; pgsql-general@postgresql.org > Subject: [GENERAL] Indexing question > > In SQL, g

Re: [GENERAL] Indexing question

amit sehas wrote: > 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 f2>, instead the

Re: [GENERAL] Indexing MS/Open Office and PDF documents

Word documents can be processed by Abiword into any msword document into html, latex, postscript, text formats with very simple commands; i guess it also exposes some api which can be integrated into document parsers/indexers. Spreadsheets can be processed by utilizing *ExcelFormat *library http:/

Re: [GENERAL] Indexing MS/Open Office and PDF documents

On Thu, Mar 15, 2012 at 4:12 PM, Jeff Davis wrote: > On Fri, 2012-03-16 at 01:57 +0530, alexander.bager...@cognizant.com > wrote: >> Hi, >> >> We are looking to use Postgres 9 for the document storing and would >> like to take advantage of the full text search capabilities. We have >> hard time id

Re: [GENERAL] Indexing MS/Open Office and PDF documents

On 15/03/12 21:12, Jeff Davis wrote: On Fri, 2012-03-16 at 01:57 +0530, alexander.bager...@cognizant.com We have hard time identifying MS/Open Office and PDF parsers to index stored documents and make them available for text searching. The first step is to find a library that can parse such

Re: [GENERAL] Indexing MS/Open Office and PDF documents

On Fri, 2012-03-16 at 01:57 +0530, alexander.bager...@cognizant.com wrote: > Hi, > > We are looking to use Postgres 9 for the document storing and would > like to take advantage of the full text search capabilities. We have > hard time identifying MS/Open Office and PDF parsers to index stored > d

Re: [GENERAL] Indexing strategy

On 04/17/2011 03:52 AM, Phoenix Kiula wrote: Hi, Need some help here. I have a large table (200 million rows already). There are two columns. ColA ColB There is an index on ColA. It's an important column. ColB is a boolean. Either 1 or 0. For about 10% of the data, ColB is 1. Otherwise it's

Re: [GENERAL] Indexing queries with bit masks

Hey thanks.. I thought I'd share the method I came up with for updating subscriptions. Basically, as far as my code is concerned the DB uses a bitmask (at least for updates) but I abstract it through a function. First off, I have a little helper function so I don't repeat the same code a bunch o

Re: [GENERAL] Indexing queries with bit masks

2010/4/30 Mike Christensen : > Ok I've been blatantly lying, err, purposely simplifying the problem for the > sake of the original email :) > > I've read over the responses, and am actually now considering just not using > any index at all.  Here's why: > > First, this actually isn't the only thing

Re: [GENERAL] Indexing queries with bit masks

Ok I've been blatantly lying, err, purposely simplifying the problem for the sake of the original email :) I've read over the responses, and am actually now considering just not using any index at all. Here's why: First, this actually isn't the only thing on the WHERE clause. It will only query

Re: [GENERAL] Indexing queries with bit masks

On Fri, Apr 30, 2010 at 11:29 AM, Tom Lane wrote: > Peter Hunsberger writes: >> If all subscriptions are roughly equal in popularity then any single >> select should give ~ 10% of the data.  That would seem to be selective >> enough that you'd really want an index? > > My personal rule of thumb i

Re: [GENERAL] Indexing queries with bit masks

Peter Hunsberger writes: > If all subscriptions are roughly equal in popularity then any single > select should give ~ 10% of the data. That would seem to be selective > enough that you'd really want an index? My personal rule of thumb is that 10% is around the threshold where indexes stop being

Re: [GENERAL] Indexing queries with bit masks

On Fri, Apr 30, 2010 at 10:08 AM, Tom Lane wrote: > Mike Christensen writes: >> When a certain event happens, let's say event 4, I need to query for which >> users to notify.  So I'll be doing something like: > >> SELECT UserId FROM Users WHERE Subscriptions & 8; > >> My question is say there's a

Re: [GENERAL] Indexing queries with bit masks

Mike Christensen writes: > When a certain event happens, let's say event 4, I need to query for which > users to notify. So I'll be doing something like: > SELECT UserId FROM Users WHERE Subscriptions & 8; > My question is say there's a million rows in the Users table. If I have an > index on

Re: [GENERAL] indexing

ginanjar wrote: > I work on my thesis on spatial database indeksing using Btree n GiST. > Can you help to explain how the GiST and btree indexing work in > postgresql ( the algorithm ) ? Can somone show me how to write code to > know the tree level and count the root and leaf ? > thanks for the inf

Re: [GENERAL] Indexing unknown words with Tsearch2

Greg Maitrallain writes: > The problem is the writings contains many names of personnalities. For > example : Churchill (the database covers WWII). But when I try to search > for these names, nothing is found. > I tried many things, like this introduction : > http://www.sai.msu.su/~megera/post

Re: [GENERAL] Indexing a Bit String column

in C? Many thanks for your help. George. > To: oakm...@hotmail.com > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Indexing a Bit String column > From: st...@enterprisedb.com > Date: Tue, 24 Feb 2009 15:35:58 + > > > George Oakman writes: &g

Re: [GENERAL] Indexing a Bit String column

Gregory Stark writes: > Note that something like "WHERE myBitStringCol & B'101'" might be selecting > too much of your table to make an index useful anyways. If each bit is set in > half the table then you're talking about selecting 3/4 of the table in which > case a full table scan would be more

Re: [GENERAL] Indexing a Bit String column

George Oakman writes: > Is it all I need to do? Will PgSQL know how to index properly a Bit String > column? Should I build the index using a special method, e.g. > CREATE INDEX myBitStringCol_idx ON myTable USING gist(myBitStringCol); No, the default will be to build a btree index which

Re: [GENERAL] Indexing columns with low cardinality: persistent bitmap indexes?

> > Hello, > > > > I have a column with a small number of distinct values, indexing this one > > with a standard BTree is useless. How do I can index this column > > efficiently? I searched and it seems that pg doesn't support the creation of > > persistent bitmap indexes... Is that feature planne

Re: [GENERAL] Indexing columns with low cardinality: persistent bitmap indexes?

and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Mon, 12 Jan 2009 16:20:40 -0500 > From: jcasa...@systemguards.com.ec > To: regmeple...@gmail.com > Subject:

Re: [GENERAL] Indexing columns with low cardinality: persistentbitmap indexes?

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Alvaro Herrera > Sent: Monday, January 12, 2009 12:41 PM > To: Bruno Lavoie > Cc: PostgreSQL > Subject: Re: [GENERAL] Indexing columns

Re: [GENERAL] Indexing columns with low cardinality: persistent bitmap indexes?

On Mon, Jan 12, 2009 at 1:38 PM, Bruno Lavoie wrote: > Hello, > > I have a column with a small number of distinct values, indexing this one > with a standard BTree is useless. How do I can index this column > efficiently? I searched and it seems that pg doesn't support the creation of > persisten

Re: [GENERAL] Indexing columns with low cardinality: persistent bitmap indexes?

On Mon, Jan 12, 2009 at 4:16 PM, Reg Me Please wrote: > On Monday 12 January 2009 21:38:02 Bruno Lavoie wrote: >> Hello, >> >> I have a column with a small number of distinct values, indexing this >> one with a standard BTree is useless. How do I can index this column >> efficiently? I searched a

Re: [GENERAL] Indexing columns with low cardinality: persistent bitmap indexes?

On Monday 12 January 2009 21:38:02 Bruno Lavoie wrote: > Hello, > > I have a column with a small number of distinct values, indexing this > one with a standard BTree is useless. How do I can index this column > efficiently? I searched and it seems that pg doesn't support the > creation of persiste

Re: [GENERAL] Indexing columns with low cardinality: persistent bitmap indexes?

Bruno Lavoie escribió: > Hello, > > I have a column with a small number of distinct values, indexing this > one with a standard BTree is useless. How do I can index this column > efficiently? I searched and it seems that pg doesn't support the > creation of persistent bitmap indexes... It

Re: [GENERAL] Indexing problem with OFFSET LIMIT

"Merlin Moncure" <[EMAIL PROTECTED]> writes: > On Fri, Aug 29, 2008 at 4:38 PM, Oliver Weichhold <[EMAIL PROTECTED]> wrote: >> Consider this query: >> >> SELECT * FROM foo WHERE realm_id = 228 order by foo_name LIMIT 200 OFFSET >> 15000 > try this: > SELECT * FROM foo WHERE realm_id = 228 order b

Re: [GENERAL] Indexing problem with OFFSET LIMIT

On Fri, Aug 29, 2008 at 4:38 PM, Oliver Weichhold <[EMAIL PROTECTED]> wrote: > Hello > > I have problem in my applications and don't know how to fix it. > > This is the table and one of the indexes: > > CREATE TABLE foo > ( > id serial NOT NULL, > foo_name character varying(100), > realm_id i

Re: [GENERAL] Indexing problem with OFFSET LIMIT

I'm no expert at reading query plans, but I'm guessing the planner chose the other plan because your offset + limit went beyond the row estimate. Look's like it's then doing a disk based sort in the other plan which probably explain why it's slow. Someone please correct me if I'm wrong.

Re: [GENERAL] indexing - creates problem

now it is for 500 records. postgres 7.4 Debian -- call_id | integer | not null default nextval('call_log_seq'::text) agent_id | integer | call_id already has index. count(*

Re: [GENERAL] indexing - creates problem

> I am having a table with more than 1000 records, i am not having index in > that, while executing that query it occupies the processor.. 1000 rows is not much - I guess the index is not necessary at all, as the traditional sequential scan is faster than index scan (due to random access vs. seque

Re: [GENERAL] indexing tables using my owns functions

On Mon, Nov 26, 2007 at 04:54:10PM +0100, Pau Marc Munoz Torres wrote: > Hi every body > > Recently i wrote my own function into postgesql... and it works perfecly!!, > now i would like use it to index a table like this guy do at the link ( > http://www.faqs.org/docs/ppbook/r24254.htm) > > but i

Re: [GENERAL] Indexing Foreign Key Columns

On Tue, 28 Aug 2007 13:19:32 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Josh Trutwin <[EMAIL PROTECTED]> writes: > > I am curious if there are any rules of thumb for when to index a > > foreign key column? > > (You realize of course that there's already an index on the > referenced column, else

Re: [GENERAL] Indexing Foreign Key Columns

Josh Trutwin <[EMAIL PROTECTED]> writes: > I am curious if there are any rules of thumb for when to index a > foreign key column? (You realize of course that there's already an index on the referenced column, else you wouldn't have been allowed to reference it.) You need an index on the referenci

Re: [GENERAL] indexing large "text" attributes ... ERROR: maximum size is 8191

Benjamin Arai <[EMAIL PROTECTED]> writes: > The tsearch2 webpage says to do something like > SELECT intindex, strTopic FROM tblmessages > WHERE idxfti @@ to_tsquery('default', 'gettysburg & > address') > AND strMessage ~* '.*men are created equal.*'; >

Re: [GENERAL] indexing large "text" attributes ... ERROR: maximum size is 8191

To clarify, I am using the GIN index to perform the tsearch2 queries which works fine. The problem with this is that you cannot use the tsearch2 fields since they are vectors to do exact match searches. The tsearch2 webpage says to do something like SELECT intindex, strTopic FROM t

Re: [GENERAL] indexing large "text" attributes ... ERROR: maximum size is 8191

Benjamin Arai <[EMAIL PROTECTED]> writes: > I am actually creating a GIN index on another field but I need to > index the original "text" field to perform exact phrase matches. Why do you think an extra index will be useful for that? Especially a btree index? regards

Re: [GENERAL] Indexing questions: Index == key? And index vs substring - how successful?

1. Does an indexed column on a table have to be a potential primary key? Nope, create as many index as you need/must/should. I've been working with a couple of rather large tables where a common select is on a foreign key called 'cntrct_id' (Varchar(9) in format). However, the

Re: [GENERAL] Indexing questions: Index == key? And index vs substring - how successful?

On Thu, May 03, 2007 at 01:42:44PM -0700, Andrew Edson wrote: > As the title of this message suggests, I've got a couple of questions about > indexing that I'm not sure about. I've tried to take a look at the docs, but > I can't remember seeing anything on these; it's quite possible, I admit, th

Re: [GENERAL] indexing array columns

you should be able to index the way you want. In contrib there a module "cube" which does similar to what you want to 3D, extending it to 12D shouldn't be too hard... contrib/cube module implements N dimensional cube representation -- Teodor Sigaev E-mail: [EMA

Re: [GENERAL] indexing array columns

Rajarshi Guha <[EMAIL PROTECTED]> writes: > select A from theTable where sim(B, C) > 0.8 > I realize that my table is essentially a collection of 12-dimensional > points and that I could replace my similarity function with a distance > function. > Thus my query boils down to asking 'find me rows

Re: [GENERAL] indexing array columns

On Fri, Apr 13, 2007 at 06:09:50PM -0400, Rajarshi Guha wrote: > Hi, I have a table of about 10M rows. It has two columns A and B, where > A is a text field and B is a real[12] field. > > Now when this table is queried it is usually of the form: > > select A from theTable where sim(B, C) > 0.8

Re: [GENERAL] indexing primary and foreign keys w/lookup table

On Wed, Jan 24, 2007 at 20:14:07 -0800, Neal Clark <[EMAIL PROTECTED]> wrote: > I was wondering...I currently have indexes on the primary key id and > foreign key id's for tables that resemble the following. Is this a > good idea/when would it benefit me? I don't want waste a lot of > unnec

Re: [GENERAL] indexing primary and foreign keys w/lookup table

On 1/25/07, Neal Clark <[EMAIL PROTECTED]> wrote: I was wondering...I currently have indexes on the primary key id and foreign key id's for tables that resemble the following. Is this a good idea/when would it benefit me? I don't want waste a lot of unnecessary space on indexes. CREATE TABLE s

Re: [GENERAL] indexing primary and foreign keys w/lookup table

CREATE TABLE stuff_by_account ( account_idBIGINT REFERENCES accounts(id), stuff_idBIGINT REFERENCES stuff(id) ); CREATE INDEX stuff_by_account_account_id ON stuff_by_account(account_id); CREATE INDEX stuff_by_account_stuff_id ON stuff_by_account(stuff_id); do I need any/all of t

Re: [GENERAL] Indexing Question

On Thu, Jan 12, 2006 at 01:16:00PM -0800, Mike wrote: > Hi, > > My table structure is the following: > > tbl_A one-to-many---> tbl_B one-to-many---> tbl_C > > Since it was important for me to trace back tbl_C records back to > tbl_A, I placed a tbl_A_id inside tbl_C. > > Now, in order t

Re: [GENERAL] [indexing] 2d spatial + 1d value in one index?

On Tue, Nov 29, 2005 at 01:03:39PM +0100, Martijn Meijers wrote: > Dear list members, > > > Let me first introduce myself, I'm Martijn and currently doing my master's > thesis research at Delft University (The Netherlands). I am working in the > field of spatial databases and I am going to test,

Re: [GENERAL] indexing date_part

Jeff Davis <[EMAIL PROTECTED]> writes: > In this case you just have a syntax error in your function. You have > tried to use a single-quoted string inside of a single-quoted string. > Escape the single quotes by writing "''" instead of "'" inside the > function definition. BTW, if you were working

Re: [GENERAL] indexing date_part

In this case you just have a syntax error in your function. You have tried to use a single-quoted string inside of a single-quoted string. Escape the single quotes by writing "''" instead of "'" inside the function definition. For example: CREATE FUNCTION month_idxable(date) returns date AS 'SELE

Re: [GENERAL] indexing just a part of a string

In the last exciting episode, [EMAIL PROTECTED] (Christoph Pingel) wrote: > I'm new to PostgreSQL, and it has been a pleasure so far. > > There's just one thing I'm trying to do and I didn't find any hints in > the manual: I want to index just a part of a string in a column. > > The situation: I ha

Re: [GENERAL] indexing just a part of a string

You can use a functional index. Something like CREATE INDEX foo ON bar (substring(blah,1,200)) Should work I think. Ian Harding Programmer/Analyst II Tacoma-Pierce County Health Department [EMAIL PROTECTED] Phone: (253) 798-3549 Pager: (253) 754-0002 >>> Christoph Pingel <[EMAIL PROTECTED]> 0

Re: [GENERAL] indexing just a part of a string

Christoph Pingel wrote: So I would like to say 'index only the first 200 chars of the column', which will result in a full index of 99.9 % of my entries. I did this in MySQL, but I didn't find it in the pg manual. How do I proceed? You could do: CREATE INDEX ON (SUBSTRING(, 1, 200)) But that m

Re: [GENERAL] Indexing Order By columns

On Sun, 2004-09-05 at 16:30, Hadley Willan wrote: > Hi all, > Can it speed things up to index the order by columns? Yes, note that you need to index on what function you might be sorting by as well: create index test_dx on test (lower(name)); will allow indexes to be used on an order by lowe

Re: [GENERAL] Indexing Order By columns

[EMAIL PROTECTED] (Hadley Willan) wrote: >     Can it speed things up to index the order by columns? Certainly. -- If this was helpful, rate me http://cbbrowne.com/info/linuxdistributions.html "Microsoft is sort of a mixture between the Borg and the Fere

Re: [GENERAL] indexing lat lon

"Jonathan Raemdonck" <[EMAIL PROTECTED]> writes: > These are the steps I take now: > 1 'draw' a box around the lat/lon position we have as input > 2 search the DB for all the points in this box > 3 measure the distance to each point in the box Right. All you need is an index amenable to step 2.

Re: [GENERAL] Indexing null dates

Tom Lane said: > "John Sidney-Woollett" <[EMAIL PROTECTED]> writes: >> [ needs to make this fast: ] >> -- locate the next order >> select WCCustOrderID into vCustOrderID >> from CUSTOMER.WCCustOrderStatusLog >> where WCOrderStatusID = pStatusID >> and Acknowledged is null >> and Process

Re: [GENERAL] Indexing null dates

"John Sidney-Woollett" <[EMAIL PROTECTED]> writes: > [ needs to make this fast: ] > -- locate the next order > select WCCustOrderID into vCustOrderID > from CUSTOMER.WCCustOrderStatusLog > where WCOrderStatusID = pStatusID > and Acknowledged is null > and Processing is null > for upda

Re: [GENERAL] indexing with lower(...) -> queries are not optimised very well - Please Help

> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: 16 January 2004 21:50 > To: Martin Hampl > Cc: [EMAIL PROTECTED]; Mark Cave-Ayland > Subject: Re: [GENERAL] indexing with lower(...) -> queries > are not optimised very well - Please He

Re: [GENERAL] indexing with lower(...) -> queries are not optimised very well - Please Help

Am 21.11.2003 um 06:54 schrieb Tom Lane: Martin Hampl <[EMAIL PROTECTED]> writes: Index Scan using word_lower_idx on token (cost=0.00..98814.08 rows=25382 width=16) Index Cond: (lower((word)::text) = 'saxophone'::text) The rows estimate (and therefore also the cost estimate) is a complete g