Re: [SQL] index problem
Hi, Stephan Szabo wrote: > On Tue, 16 Oct 2001, CoL wrote: > > >>--- >>The 2 table query, where prog_data has ~8800 rowsn and index on prog_id: >>bash-2.04$ time echo "explain select distinct >>prog_id,prog_ftype,prog_fcasthour,prog_date from prog_dgy_xy,prog_data >>where pxygy_pid=prog_id " | psql -Uuser db >>NOTICE: QUERY PLAN: >> >>Unique (cost=7432549.69..7680455.07 rows=2479054 width=32) >> -> Sort (cost=7432549.69..7432549.69 rows=24790538 width=32) >> -> Merge Join (cost=148864.65..161189.33 rows=24790538 width=32) >> -> Index Scan using prog_data_pkey on prog_data >>(cost=0.00..701.12 rows=8872 width=28) >> -> Sort (cost=148864.65..148864.65 rows=921013 width=4) >> -> Seq Scan on prog_dgy_xy (cost=0.00..30145.13 >>rows=921013 width=4) >> > > I'm guessing that the approximately 25 million row estimate on the join > has to be wrong as well given that prog_data.prog_id should be unique. > > Hmm, does the explain change if you vacuum analyze the other table > (prog_data)? If not, what does explain show if you do a > set enable_seqscan='off'; > before it? The result: db=>set enable_seqscan='off'; db=>explain select distinct prog_id,prog_ftype,prog_fcasthour,prog_date from prog_dgy_xy,prog_data where pxygy_pid=prog_id; NOTICE: QUERY PLAN: Unique (cost=7606982.10..7854887.48 rows=2479054 width=32) -> Sort (cost=7606982.10..7606982.10 rows=24790538 width=32) -> Merge Join (cost=0.00..335621.73 rows=24790538 width=32) -> Index Scan using progdgyxy_idx2 on prog_dgy_xy (cost=0.00..323297.05 rows=921013 width=4) -> Index Scan using prog_data_pkey on prog_data (cost=0.00..701.12 rows=8872 width=28) It "seems" index is used, but the same result :(((, and bigger execution time: real 3m41.830s What is in tables? prog_data contains unique id and other info. prog_dgy_xy contains that id with x,y coordinates (so many ids from prog_data with unique x,y) #prog_data: #prog_id, prog_ftype, prog_fcasthour, prog_date #1 'type'6 2001-10-14 12:00:00 #2 'type'12 2001-10-14 12:00:00 #prog_dgy_xy: #pxygy_pid, pxygy_x, pxygy_y #1 0.1 0.1 #1 0.1 0.15 How can this query takes real0m1.755s for mysql, [17 sec for oracle], and 2-3 minutes!! for postgres? And why: POSTGRES: set enable_seqscan ='off'; select count(*) from prog_dgy_xy where pxygy_pid<13161; count 900029 real2m34.340s explain: Aggregate (cost=327896.89..327896.89 rows=1 width=0) -> Index Scan using progdgyxy_idx2 on prog_dgy_xy (cost=0.00..325594.54 rows=920940 width=0) MYSQL: select count(pxygy_pid) from PROG_DGY_XY where pxygy_pid<13161 count(pxygy_pid) 900029 real0m27.878s explain: table typepossible_keys key key_len ref rowsExtra PROG_DGY_XY range progdgyxy_idx1,progdgyxy_idx2 progdgyxy_idx2 4 NULL906856 where used; Using index The same time difference in case of: = or >, however explain says, cause seq scan is off, the index is used. I did vacuum, and vacuum analyze too before. PS: I think i have to make a site for that, cause there are many questions :), and weird things. I love postgres but this makes me "hm?". Today i'll make these test under 7.1.2. thx CoL ---(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] index problem
Hi, I did not make vacuum analyze ;), the vacuum once now: vacuumdb -Uuser -ddb -v -tprog_dgy_xy NOTICE: --Relation prog_dgy_xy-- NOTICE: Pages 20935: Changed 0, reaped 0, Empty 0, New 0; Tup 921013: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 178, MaxLen 184; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 2.71s/0.32u sec. NOTICE: Index progdgyxy_idx1: Pages 6679; Tuples 921013. CPU 1.41s/1.40u sec. NOTICE: Index progdgyxy_idx2: Pages 2019; Tuples 921013. CPU 0.28s/1.28u sec. I make it with -z too. So this table has more 921013 rows. The query show the same as bellow. The version is 7.1.3. - One more interesting: the insering of these rows. Postgres: bash-2.04$ time psql -q -Uuser -f prog_dgy_xy.dump db real131m50.006s user3m21.838s sys 1m20.963s Mysql: bash-2.04$ time cat prog_dgy_xy.dump | mysql -uuser -ppass db real24m50.137s user2m6.629s sys 1m37.757s the dump file was: insert into table (...) values (...); I tried with copy, and to add begin; inserts; commit; , but the result with same time :( [For Oracle 8.1.6 sqlloader it takes 450 sec ;) ] --- The 2 table query, where prog_data has ~8800 rowsn and index on prog_id: bash-2.04$ time echo "explain select distinct prog_id,prog_ftype,prog_fcasthour,prog_date from prog_dgy_xy,prog_data where pxygy_pid=prog_id " | psql -Uuser db NOTICE: QUERY PLAN: Unique (cost=7432549.69..7680455.07 rows=2479054 width=32) -> Sort (cost=7432549.69..7432549.69 rows=24790538 width=32) -> Merge Join (cost=148864.65..161189.33 rows=24790538 width=32) -> Index Scan using prog_data_pkey on prog_data (cost=0.00..701.12 rows=8872 width=28) -> Sort (cost=148864.65..148864.65 rows=921013 width=4) -> Seq Scan on prog_dgy_xy (cost=0.00..30145.13 rows=921013 width=4) Time: !!! real2m3.620s the same query with mysql (i did explain in mysql, and says it use the indexes): real0m1.998s !!! I just askin why? and why just using the index on releation "=". (same table, same index, vacuumed) (made the test more than twice) It seams to be a 7.1.3 bug? i do not test yet with 7.1.2 but tomorrow i will. CoL Stephan Szabo wrote: > On Mon, 15 Oct 2001, Szabo Zoltan wrote: > > >>Hi, >> >>I have that: >> >>1) >>db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid=12121; >>NOTICE: QUERY PLAN: >> >>Group (cost=0.00..29970.34 rows=921 width=4) >> -> Index Scan using progdgyxy_idx2 on prog_dgy_xy >>(cost=0.00..29947.32 rows=9210 width=4) >> >>than: >>2) >>db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid>12121; >>NOTICE: QUERY PLAN: >> >>Group (cost=66927.88..67695.39 rows=30700 width=4) >> -> Sort (cost=66927.88..66927.88 rows=307004 width=4) >> -> Seq Scan on prog_dgy_xy (cost=0.00..32447.66 rows=307004 >>width=4) >> >>I making some banchmarks on: oracle vs postgres vs mysql. And this is >>breaking me now;) Mysql and oracle width same table and index use that >>index on pxygy_pid; >>I had vacuum before. >> > > I assume you mean you did a vacuum analyze (a plain vacuum isn't > sufficient). If you did just do a regular vacuum, do a vacuum analyze > to get the updated statistics. > > How many rows actually match pxygy_pid>12121? Is 307000 rows a reasonable > estimate? How many rows are in the table? > > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Two Permance Questions
The select without subselect of course is better, and faster. Check your "explain analyze select ..." to se the performance, and to check the index usage if any. C. CN LIOU wrote: > Hi! > > Q1. Is subquery better or join? > > For subquery: > > SELECT t1.c1,(SELECT t2.c2 FROM t2 WHERE t2.c1 = t1.c1) FROM t1 > > I wonder it will loop n times if t1 returns n rows. If this is the case, is it >better to use join like this: > > SELECT t1.c1,t2.c2 FROM t1,t2 WHERE t2.c1 = t1.c1 > > Q2. If the query is not optimize like this: > > SELECT t1.c1,t2.c2 FROM t1,t2,t1,t2,t2 WHERE t2.c1=t1.c1 AND t1.c1=t2.c1 AND >t1.c1=t2.c1 > > and the size of this clause can soar up to several kbytes, then can this query cause >performance problem? > > Regards, ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] lastoid from sql
Hi, how can I get tha lastoid variable from sql? thx. C. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] lastoid from sql
select :LASTOID; sorry! :) and thx;) CoL wrote: > Hi, > > how can I get tha lastoid variable from sql? > > thx. > > C. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Restore deleted records
Hi, Konstantin Petrenko wrote, On 8/26/2003 11:37 AM: Hello. I accidentally deleted some recordes from my table. How can I restore them? Is it possible in 7.3.3? if you have a dump, or you are still in a transaction, you can rollback. C. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] URGENT!!! changing Column size
Hi, [EMAIL PROTECTED] wrote, On 10/27/2003 6:10 PM: Hi can we change the size of a column in postgres. I have a table named institution and column name is name varchar2(25), i want to change it to varchar2(50). Please let me know. 1 solution: begin; create temporary table temp as select * from mytable; drop table mytable; create table mytable (name varchar(50)); insert into mytable select CAST(name AS varchar(50)) from temp; drop table temp; commit; C. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Validity check in to_date?
hi,
SELECT isfinite(timestamp '123.45.2003'); if this is true, the date is
ok, if error, than not :)
C.
Alexander M. Pravking wrote:
I just discovered that to_date() function does not check if supplied
date is correct, giving surprising (at least for me) results:
fduch=# SELECT to_date('31.11.2003', 'DD.MM.');
to_date
2003-12-01
or even
fduch=# SELECT to_date('123.45.2003', 'DD.MM.');
to_date
2007-01-03
---(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] XML & Postgres Functions
hi, zerobearing2 wrote: Hi all- I'm migrating to postgres from the MS SQL Server land, as I can see a great potential with postgres, I was wondering if anyone has experimented or started a project with XML inside user defined functions? I've seen the contrib/xml shipped with the distro, as I see it's usefulness, it's not quite what I had in mind (lacks the ablity to return recordset/table of nodes). Also, the project XpSQL seems interesting, but still not it. I'm looking for a solution similar to the OpenXML that MS implements in their SQL Server. A way to parse XML into a table and/or a temp table for use inside a function. For example, I would like to serialize my objects into XML, send them to a postgres function for saving/updating the relational data inside postgres. I envision something of the following could be useful: XML -- ... UDF -- create function somefunc(xmldata text) begin xmldoc := preparedoc(xmldata); update tablename set field1=x.field1, field2=x.field2, from xmltable(xmldoc, '/table/[EMAIL PROTECTED]') as x preform removedoc(xmldoc); end; Check the xml dir under contrib. Or/And http://www.google.com/search?hl=en&lr=&ie=UTF-8&oe=utf-8&q=xml+in+postgres+site%3Apostgresql.org C. ---(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] How to specify the beginning of the month in Postgres SQL syntax?
hi [EMAIL PROTECTED] wrote, On 12/7/2003 5:16 PM: Hello, I need to create a view in Postgres that has a where clause of the date < beginning of month. i.e.: SELECT supplier_number, Sum(amount) AS due FROM purchase_orders WHERE date < '2003-12-1' AND paid = 0 GROUP BY supplier_number ORDER BY supplier_number ASC As you can see, I've specified the 1st of December this year as the where clause. What I want is an SQL statement that automatically generates the first of the month. How do I do this? a simple way: create function month_begin() returns date as ' SELECT to_date(current_date,'-MM'); ' language sql STABLE; ... where date < month_begin() C. ---(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] testing for null value in integer field?
try: column is null, as manual shows. Geoffrey wrote: How does one check for an unset value in an integer field? I've tried such things as: select . where intnumber = '' select .. where intnumber = ? select . where intnumber = NULL Thanks. C. ---(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] Storing a range of numbers
hi, Chris Fisher wrote: Hi, Is it possible to store a numeric range in a row and query for it, or would I have to use two rows containing the minimum and maximum limits? or you can have one column, with array type. than the first element is the min value the second is the max:) Question is: is it good for your applications or not, how woud you use it, etc. C. ---(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] Concatenate results of a single column query
hi, Christoph Haller wrote: Interesting feature, but I cannot find function array_append: ERROR: AggregateCreate: function array_append(integer[], integer) does not exist try with pg 7.4 C. ---(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] Function for numbering rows?
hi,
Tony Reina wrote, On 4/10/2004 18:12:
Simple question:
Is there a function that will allow me to number the returned tuples?
e.g.
CREATE TABLE test (
field1 text
):
INSERT INTO test VALUES ('first');
INSERT INTO test VALUES ('second');
INSERT INTO test VALUES ('third');
INSERT INTO test VALUES ('fourth');
SELECT number(), field1 FROM test;
1 'first'
2 'second'
3 'third'
4 'fourth'
where number() is the function I'm trying to find out about.
a simple way:
create temporary sequence st;
select nextval('st'),field1 from test;
drop sequence st;
C.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] problem porting MySQL SQL to Postgres
hi, Dan Field wrote: I've stumbled across a query I don't quite understand the error message for. This query is pulled from a working MySQL setup: SELECT DEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, DEWEY_POINT_ONES, DEWEY_POINT_TENS, DEWEY_POINT_HUNDREDS, DEWEY_POINT_THOUSANDS, DEWEY_TYPE, DEWEY_LANG, DEWEY_SUBJECT FROM lu_dewey WHERE (DEWEY_HUNDREDS = 9) AND (DEWEY_TENS >= 0) AND (DEWEY_TENS <= 9) AND (DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND (DEWEY_POINT_ONES = 0 || DEWEY_POINT_ONES = NULL) AND (DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND (DEWEY_POINT_HUNDREDS = 0 || DEWEY_POINT_HUNDREDS = NULL) AND (DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL) AND (DEWEY_TYPE = 't') AND (DEWEY_LANG = 'en') ORDER BY DEWEY_TENS However I'm getting the following error: ERROR: Unable to identify an operator '=' for types 'character' and 'boolean' You will have to retype this query using an explicit cast. use OR not ||. C. ---(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] Update is very slow on a bigger table
hi, Dimitar Georgievski wrote, On 4/15/2004 23:07: hi, i'm running an update procedure on a table with approximately 100,000 records. The problem is the procedure is running endlessly even when the update is very simple like in the following example: update mytable set client_id = 1 where mytable_id = 1 mytable_id is the primary key of mytable. the process under which the update is running reports among the usual process data "UPDATE waiting". I'm using PostgreSQL 7.3.4 on SuSE 9.0. From what I have seen it doesn't look like that the host computer has problem with the resources. Any idea why is this happening? could be, after you send some info: your table schema, your explain analyze result, triggers, or any other info. Any other transaction running again mytable while you try to update? C. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Subqueries returning more than one value?
hi, Adam Witney wrote, On 5/11/2004 02:09: Hi, I am using a function in a subquery, this works ok: SELECT name, (SELECT p_my_func(1)) AS id FROM test; However I would like to have the function return 2 values into the main query... Something like this: SELECT name, (SELECT p_my_func(1)) AS (id, type) FROM test; Of course this gives the error: ERROR: subquery must return only one column Is there any way around this? SELECT name,t.* FROM (SELECT p_my_func(1)) AS t(id, type), test C. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Check a value in array
hi, Marco Lazzeri wrote: Il gio, 2004-04-29 alle 19:56, Rod Taylor ha scritto: On Thu, 2004-04-29 at 13:37, Marco Lazzeri wrote: Hi all. I have to check if a value is in an array. I've got a date array in a table and I would like to perform queries like: SELECT * FROM table WHERE date IN dates_array; If you're using 7.4 or later, try: SELECT * FROM table WHERE date = ANY(dates_array); This will work without the contrib package. Thank you Rod, I forgot to specify I'm using PostgreSQL 7.3.5. or, you can write a procedure, and make and it immutable:) CREATE OR REPLACE FUNCTION in_array (numeric [], numeric) RETURNS boolean AS' declare array_to alias for $1; array_value alias for $2; i integer default 1; begin while array_to[i] is not null loop if array_value = array_to[i] then return true; end if; i := i+1; end loop; return false; end; 'LANGUAGE 'plpgsql' immutable RETURNS NULL ON NULL INPUT SECURITY INVOKER; change the numeric to your specified type and: WHERE in_array(dates_array,date) = true C. ---(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] isnumeric() function?
hi, Yudie wrote: > What is isnumeric function in postgresql? > I'm using psql version 7.2.2 > thanks probably somebody write a function called isnumeric for you :) So it must be a user defined function. C. ---(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] multi results with functions
hi, kasper wrote, On 5/31/2004 00:02: Hi Im trying to make a stored procedure that returns * from at table, and returns multible rows. Can you do that, and how? basically I would like to do: create or replace function test () returns record as ' select * from dummytable; ' language sql; returns SETOF dummytable as ' C. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Calling a table residing in another database from Plpgsql
hi,
[EMAIL PROTECTED] wrote, On 1/5/2005 06:13:
I infact downloaded the dblink package in contrib folder. But still when i
tried to use dblink in the following manner:
SELECT * from dblink('host=192.168.0.50 dbname=AK1 user=akteam','Select
userid from user_reg') as t (userid integer);
I am getting an error as "ERROR: function dblink("unknown", "unknown")
does not exist"
try to set up dblink. Read the README.dblink file, Installation: part.
Use dblink_connect to connect, then you can query with dblink()
read the docs befor you try to use it.
PS: or better use schemas, not different databases.
C.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] VIEW / ORDER BY + UNION
hi, WeiShang wrote, On 2/17/2005 16:46: Hi, I have created a view like this : CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where t1.orderno=t2.orderno); if I create a SQL statment: (SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno) UNION (SELECT orderno FROM v1 WHERE weekday='WED' ORDER BY orderno) UNION (SELECT orderno FROM v1 WHERE weekday='FRI' ORDER BY orderno); Will the whole result will be sorted by the field orderno? nothing says it will. you havet to sort the set of unions. (select orderno ) union (select orderno ) order by orderno. Not necessary now (no sense), to use order by in selects inside union. C. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to Port Oracle's user defined "Package" into Postgres 8.0.1.
hi, Dinesh Pandey wrote, On 4/11/2005 15:39: Hi folks, Can any one give me an idea about: How to Port Oracle's user defined "Package" into Postgres 8.0.1. there is no Package in postgresql. You have to rewrite the logic, extract the functions from Package and recreate them for postgres. But you can't port 1:1 oracle packages to pg. C. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
