Re: [SQL] [HACKERS] please help on query
- Original Message - From: "Masaru Sugawara" <[EMAIL PROTECTED]> To: "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, July 14, 2002 2:23 PM Subject: Re: [SQL] [HACKERS] please help on query This is the output: Aggregate (cost=0.00..647161.10 rows=600122 width=8) (actual time=4959.19..347328.83 rows=62 loops=1) -> Group (cost=0.00..632158.04 rows=6001225 width=8) (actual time=10.79..274259.16 rows=6001225 loops=1) -> Index Scan using lineitem_pkey on lineitem (cost=0.00..617154.97 rows=6001225 width=8) (actual time=10.77..162439.11 rows=6001225 loops=1) Total runtime: 347330.28 msec it is returning all rows in lineitem. Why is it using index? Thanks and regards > On Fri, 12 Jul 2002 17:32:50 +0200 > "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]> wrote: > > > > Lineitem is being modified on run time, so creating a temp table don't > > solves my problem > > The time of creating this table is the same of performing the subselect (or > > so I think), it could be done creating a new table, and a new trigger, but > > there are already triggers to calculate > > lineitem.extendedprice=part.retailprice*lineitem.quantity*(1+taxes)*(1-disco > > unt) and to calculate orderstatus in order with linestatus and to calculate > > orders.totalprice as sum(extendedprice) where > > lineitem.orderkey=new.orderkey. A new trigger in order to insert orderkey if > > sum(quantity) where orderkey=new.orderkey might be excessive. > > Any other idea? > > Thanks And Regards > > > > - Original Message - > > From: "Jakub Ouhrabka" <[EMAIL PROTECTED]> > > To: "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]> > > Cc: "Manfred Koizar" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Friday, July 12, 2002 1:50 PM > > Subject: Re: [SQL] [HACKERS] please help on query > > > > > > > > avoid subselect: create a temp table and use join... > > > > > > CREATE TEMP TABLE tmp AS > > >SELECT > > > lineitem.orderkey > > >FROM > > > lineitem > > >WHERE > > > lineitem.orderkey=orders.orderkey > > >GROUP BY > > > lineitem.orderkey HAVING > > > sum(lineitem.quantity)>300; > > > Hi, > > I'm not sure whether its performance can be improved or not. But I feel > there is a slight chance to reduce the total number of the tuples which > Planner must think. > > BTW, how much time does the following query take in your situation, > and how many rows does it retrieve ? > > > EXPLAIN ANALYZE > SELECT > lineitem.orderkey > FROM > lineitem > GROUP BY > lineitem.orderkey > HAVING > SUM(lineitem.quantity) > 300; > > > > Regards, > Masaru Sugawara > > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] sql question:
hi all: I'm trying to figure out SQL to do the following: I have an application that tracks SQL that is being sent to the database, and one of it's features is the ability to identify whether a query is an insert, update, delete, select, select with all rows returned, the query is the first in a user sessionand many other criteria. Because of the nature of SQL, i.e. many of the above could be true, the deisgners made each flag a 'bit'. So an example is: 4 is a select 8 is insert 16 is update 32 is first query in session 64 is delete 128 is a cancelled query 256 is database cancelled query Now the SQL that I have to find is 'which of these records is a delete?' The values could be 64, 96, 416, 445, 320 and many others. All in all there are probably 20 possible values and the permutations are to lengthy to put in a 'like', so I need some kind of algorithm. Does anyone have any ideas? email: [EMAIL PROTECTED] thanks, Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] line datatype
Probably the most succinct explanation would be to copy & paste from the terminal... tjhart=> create table a_line( foo line ); CREATE tjhart=> insert into a_line ( foo ) values( '(0,0), (1,1)' ); ERROR: line not yet implemented tjhart=> select version(); version - PostgreSQL 7.2.1 on powerpc-apple-darwin5.3, compiled by GCC 2.95.2 (1 row) The documentation (datatype-geometric.html) indicates both a 'line' type and an 'lseg' type in the summary table at the top of the page. The same code above using the type 'lseg' in place of 'line' works just fine. Why can I create a table with a column of type 'line' if I can't insert into it? ---(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] sql question:
Chris Aiello wrote:
> hi all:
>
> I'm trying to figure out SQL to do the following:
> I have an application that tracks SQL that is being sent to the database,
> and one of it's features is the ability to identify whether a query is an
> insert, update, delete, select, select with all rows returned, the query is
> the first in a user sessionand many other criteria. Because of the
> nature of SQL, i.e. many of the above could be true, the deisgners made each
> flag a 'bit'. So an example is:
> 4 is a select
> 8 is insert
> 16 is update
> 32 is first query in session
> 64 is delete
> 128 is a cancelled query
> 256 is database cancelled query
>
>
>
> Now the SQL that I have to find is 'which of these records is a delete?'
> The values could be 64, 96, 416, 445, 320 and many others. All in all
> there are probably 20 possible values and the permutations are to lengthy to
> put in a 'like', so I need some kind of algorithm. Does anyone have any
> ideas?
The algorithm is as simple as that:
if (value & 64 == 64) {
// it is a delete
}
--
Ahti Legonkov
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Indexes with LIKE
From: "Stephan Szabo" <[EMAIL PROTECTED]> > You need to have made the database in C locale in order to get index scans > from LIKE. I think that's mentioned in the Localization section of the > admin guide, but I could be remembering that wrong. Thanks very much Stephan. Indeed it's in Admin Guide 5.1.2. I was using en_GB, not C. There is of course no excuse for failing to read every bit of smallprint in the admin guide before installation :-) but for those lazy unfortunates like me that don't think to look there to find out what's wrong when indexes aren't used with LIKE, I've added a DocNote under Pattern Matching. Julian Scarfe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] line datatype
Tim Hart wrote: > Probably the most succinct explanation would be to copy & paste from the > terminal... > > tjhart=> create table a_line( foo line ); > CREATE > tjhart=> insert into a_line ( foo ) values( '(0,0), (1,1)' ); > ERROR: line not yet implemented > tjhart=> select version(); > version > - > PostgreSQL 7.2.1 on powerpc-apple-darwin5.3, compiled by GCC 2.95.2 > (1 row) > > > The documentation (datatype-geometric.html) indicates both a 'line' type > and an 'lseg' type in the summary table at the top of the page. The same > code above using the type 'lseg' in place of 'line' works just fine. > > Why can I create a table with a column of type 'line' if I can't insert > into it? Well, that's a very good question. I see you have to compile PostgreSQL with ENABLE_LINE_TYPE defined in pg_config.h.in and rerun configure. I see this commit from August 16, 1998: revision 1.35 date: 1998/08/16 04:06:55; author: thomas; state: Exp; lines: +7 -6 Disable not-ready-to-use support code for the line data type. Bracket things with #ifdef ENABLE_LINE_TYPE. The line data type has always been used internally to support other types, but I/O routines have never been defined for it. psql \dT clearly shows line and lseg: line| geometric line '(pt1,pt2)' lseg| geometric line segment '(pt1,pt2)' so I think we have both a documentation problem, psql problem, and code problem. Let's see what Thomas says. For the short term, I would use lseg because it looks the same. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] pg_restore cannot restore index
Same problem, did you test: pg_restore --index=aa --dbname=test /bjm/x ?? I didn't make it work, I may miss someting. Thanks! Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 7:51 AM To: Jie Liang Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: pg_restore cannot restore index Jie Liang wrote: > On this point, I'd like to ask: > 1. where I can download this new version? > 2. does > pg_restore --index=aa --dbname=test /bjm/x > works also??? OK, the attached patch should allow -I to work in 7.2.X. This will all be fixed in 7.3. > Because > pg_restore --table=mytable --dbname=mydb mydumpfile > doesn't work! Is this a different problem? --table doesn't work either? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] [HACKERS] please help on query
On Mon, 15 Jul 2002 09:45:36 +0200 "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]> wrote: > This is the output: > > Aggregate (cost=0.00..647161.10 rows=600122 width=8) (actual > time=4959.19..347328.83 rows=62 loops=1) > -> Group (cost=0.00..632158.04 rows=6001225 width=8) (actual > time=10.79..274259.16 rows=6001225 loops=1) > -> Index Scan using lineitem_pkey on lineitem > (cost=0.00..617154.97 rows=6001225 width=8) (actual time=10.77..162439.11 > rows=6001225 loops=1) > Total runtime: 347330.28 msec > > it is returning all rows in lineitem. Why is it using index? Sorry, I don't know the reason. I need more info. Can you show me the outputs of EXPLAIN ANALYZE ? EXPLAIN ANALYZE SELECT orders.orderkey FROM lineitem LEFT OUTER JOIN orders USING(orderkey) WHERE orders.orderkey IS NOT NULL GROUP BY orders.orderkey HAVING SUM(lineitem.quantity) > 300; EXPLAIN ANALYZE SELECT t2.* FROM (SELECT orders.orderkey FROM lineitem LEFT OUTER JOIN orders USING(orderkey) WHERE orders.orderkey IS NOT NULL GROUP BY orders.orderkey HAVING SUM(lineitem.quantity) > 300 ) AS t1 LEFT OUTER JOIN orders AS t2 USING(orderkey) ORDER BY t2.custkey Regards, Masaru Sugawara ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL]
Hi, Can any of you hackers tell me where postgres looks for pg_hba.conf when it starts up? Thanks, Ted ---(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: [SQL]
Wallingford, Ted wrote: > Hi, > > Can any of you hackers tell me where postgres looks for pg_hba.conf when it > starts up? In /data. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Seeding
Is there any way to automaticly "seed" a number into a list. For example create table "temp"( select distinct(full_phone) from lists where client_id =8) This gives me 100,000 unique records What i would like to do is, every 2500, insert a specific number like '5552552555' can this be done through sql? or what would be the best approach. TIA Chad
[SQL] Indexing UNIONs
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 between the two tables (using a sequence). However, as t1 has 100,000 records, it is vitally important that queries against this view use an index. As it is a Union view, though, they ignore any indexes: jwnet=> explain select * from two_tables where id = 101072; NOTICE: QUERY PLAN: Subquery Scan two_tables (cost=0.00..3340.82 rows=99182 width=55) -> Append (cost=0.00..3340.82 rows=99182 width=55) -> Subquery Scan *SELECT* 1 (cost=0.00..3339.81 rows=99181 width=55) -> Seq Scan on t1 (cost=0.00..3339.81 rows=99181 width=55) -> Subquery Scan *SELECT* 2 (cost=0.00..1.01 rows=1 width=28) -> Seq Scan on t2 (cost=0.00..1.01 rows=1 width=28) EXPLAIN jwnet=> explain select * from t1 where id = 101072; NOTICE: QUERY PLAN: Index Scan using t1_pkey on cases (cost=0.00..5.99 rows=1 width=150) How can I make this happen? Ideas, suggestions? And no, putting the data from both tables into one is not an option for various schema reasons. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Indexing UNIONs
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; > > This works fine as a view, since I have made the id's unique between the two > tables (using a sequence). However, as t1 has 100,000 records, it is > vitally important that queries against this view use an index. 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Sorry..
Christopher, > In the bad old days when we couldn't distinguish explicit from implicit > cast functions, I was wary of adding new cast pathways. Too many > implicit casts and you have no type system at all. But in 7.3 there > should be no reason to object to an explicit-only cast from numeric > to text or vice versa. I'd suggest making the explicit cast of numeric to text be the exact equivalent of: SELECT btrim(to_char(numeric, '999,999,999,999.99')) or similar. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pg_restore cannot restore index
I just ran some tests in 7.2.1 and 7.3 and both worked fine. Can I see the exact error it generates? --- Jie Liang wrote: > Same problem, did you test: > pg_restore --index=aa --dbname=test /bjm/x > ?? > I didn't make it work, I may miss someting. > > Thanks! > > > Jie Liang > > > > -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED]] > Sent: Saturday, July 13, 2002 7:51 AM > To: Jie Liang > Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' > Subject: Re: pg_restore cannot restore index > > > Jie Liang wrote: > > On this point, I'd like to ask: > > 1. where I can download this new version? > > 2. does > > pg_restore --index=aa --dbname=test /bjm/x > > works also??? > > OK, the attached patch should allow -I to work in 7.2.X. This will all > be fixed in 7.3. > > > Because > > pg_restore --table=mytable --dbname=mydb mydumpfile > > doesn't work! > > Is this a different problem? --table doesn't work either? > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Seeding
Chad Thompson writes: > create table "temp"( > select distinct(full_phone) > >from lists > where client_id =8) > > This gives me 100,000 unique records > > What i would like to do is, every 2500, insert a specific number like '5552552555' The first thing you're going to have to define is what you mean with "every 2500", because records in tables are not ordered. -- Peter Eisentraut [EMAIL PROTECTED] ---(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] pg_restore cannot restore index
su postgres -c "/usr/local/pgsql/bin/pg_restore --table=mytable --dbname=mydb mydumpfile" error msg pg_restore: [archiver] could not open input file: No such file or directory I run it on 7.2.0 Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 3:24 PM To: Jie Liang Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: pg_restore cannot restore index I just ran some tests in 7.2.1 and 7.3 and both worked fine. Can I see the exact error it generates? --- Jie Liang wrote: > Same problem, did you test: > pg_restore --index=aa --dbname=test /bjm/x > ?? > I didn't make it work, I may miss someting. > > Thanks! > > > Jie Liang > > > > -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED]] > Sent: Saturday, July 13, 2002 7:51 AM > To: Jie Liang > Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' > Subject: Re: pg_restore cannot restore index > > > Jie Liang wrote: > > On this point, I'd like to ask: > > 1. where I can download this new version? > > 2. does > > pg_restore --index=aa --dbname=test /bjm/x > > works also??? > > OK, the attached patch should allow -I to work in 7.2.X. This will all > be fixed in 7.3. > > > Because > > pg_restore --table=mytable --dbname=mydb mydumpfile > > doesn't work! > > Is this a different problem? --table doesn't work either? > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] how pg_restore long form works?
su postgres -c "/usr/local/pgsql/bin/pg_restore --table=mytable --dbname=mydb mydumpfile" error msg pg_restore: [archiver] could not open input file: No such file or directory I run it on 7.2.0 Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 3:24 PM To: Jie Liang Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: pg_restore cannot restore index I just ran some tests in 7.2.1 and 7.3 and both worked fine. Can I see the exact error it generates? --- Jie Liang wrote: > Same problem, did you test: > pg_restore --index=aa --dbname=test /bjm/x > ?? > I didn't make it work, I may miss someting. > > Thanks! > > > Jie Liang > > > > -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED]] > Sent: Saturday, July 13, 2002 7:51 AM > To: Jie Liang > Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' > Subject: Re: pg_restore cannot restore index > > > Jie Liang wrote: > > On this point, I'd like to ask: > > 1. where I can download this new version? > > 2. does > > pg_restore --index=aa --dbname=test /bjm/x > > works also??? > > OK, the attached patch should allow -I to work in 7.2.X. This will all > be fixed in 7.3. > > > Because > > pg_restore --table=mytable --dbname=mydb mydumpfile > > doesn't work! > > Is this a different problem? --table doesn't work either? > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pg_restore cannot restore index
Jie Liang wrote: > su postgres -c "/usr/local/pgsql/bin/pg_restore --table=mytable > --dbname=mydb mydumpfile" > error msg > pg_restore: [archiver] could not open input file: No such file or directory > > I run it on 7.2.0 OK, my guess is that the 'su' is moving you to another directory. Try specifying the full path of the file, e.g. /var/tmp/mydumpfile. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] pg_restore --flag
I tried, I got same error msg. I even run as user postgres or myself, same same. My OS=FreeBSD4.3 DB=PostgreSQL7.2 Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 4:25 PM To: Jie Liang Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: pg_restore cannot restore index Jie Liang wrote: > su postgres -c "/usr/local/pgsql/bin/pg_restore --table=mytable > --dbname=mydb mydumpfile" > error msg > pg_restore: [archiver] could not open input file: No such file or directory > > I run it on 7.2.0 OK, my guess is that the 'su' is moving you to another directory. Try specifying the full path of the file, e.g. /var/tmp/mydumpfile. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Indexing UNIONs
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 to hold the Unioned data and index that instead? I can understand the difficultyof optimization. However, the example I supplied is the simplest of unions, and the two Seq Scans do seem to be proceeding against each table seperately. I think for very simple Unions (i.e. no grouping, no filtering within subqueries, etc.) that index usage would be reasonable to implement. However, I can't program it myself, so I'll have to just stick to whining and pitiful pleading -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] Indexing UNIONs
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 is horrible with the view, I should use a dummy table to > hold the Unioned data and index that instead? Possibly. > I can understand the difficultyof optimization. However, the example I > supplied is the simplest of unions, and the two Seq Scans do seem to be > proceeding against each table seperately. I think for very simple Unions > (i.e. no grouping, no filtering within subqueries, etc.) that index usage > would be reasonable to implement. I don't think it's a difficulty of implementation thing. I'd guess that alot of the current stuff for shoving down conditions would apply (I haven't looked, though). It's more a case of making sure the optimization cannot be a false one that changes the results. What we need is someone to sit down and analyze the cases in a serious way. I think that for union all, conditions other than non-stable ones can be pushed down. For union, I think there might be issues due to the removal of duplicates in certain cases where the results will change, but that the results may not be deterministic in such cases anyway (like a case where two values are not exactly the same but aren't distinct due to collation or some such and so the system picks an arbitrary one and that arbitrary one affects the output of query). I have no good idea for EXCEPT and INTERSECT. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] line datatype
OK, I have added comments to \dT and SGML docs to mention that 'line' is not implemented. This should help future folks. It would be nice to get the line type working 100%. Thomas says the problem is input/output format. I don't completely understand. --- Tim Hart wrote: > Probably the most succinct explanation would be to copy & paste from the > terminal... > > tjhart=> create table a_line( foo line ); > CREATE > tjhart=> insert into a_line ( foo ) values( '(0,0), (1,1)' ); > ERROR: line not yet implemented > tjhart=> select version(); > version > - > PostgreSQL 7.2.1 on powerpc-apple-darwin5.3, compiled by GCC 2.95.2 > (1 row) > > > The documentation (datatype-geometric.html) indicates both a 'line' type > and an 'lseg' type in the summary table at the top of the page. The same > code above using the type 'lseg' in place of 'line' works just fine. > > Why can I create a table with a column of type 'line' if I can't insert > into it? > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
