Re: [SQL] Slow Query problem
I already install the latest version of PostgreSQL on my machine then try again. I found that it has a little improvement about 0.5 second but I think it still slow. What is the 'more detail' you need? Any other advise? >>> Tom Lane <[EMAIL PROTECTED]> 1/29/2008 0:20 >>>Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:> On Monday 28 January 2008 08:18:24 Premsun Choltanwanich wrote:>> I am currently using PostgreSQL database version 8.0.13. My problem>> relates to a slow result when a query using a defined view joins to another>> table for a result.> It is very hard to help without you providing the schema for the tables/views> involved.One suggestion is that 8.2.x is significantly smarter about joins tounions than previous releases were. Whether this would help in yourparticular case is impossible to say, though, without more detail.regards, tom lane---(end of broadcast)---TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match NETsolutions Asia Limited +66 (2) 237 7247
Re: [SQL] Slow Query problem
On Tuesday 29 January 2008 09:18:00 Premsun Choltanwanich wrote: > I already install the latest version of PostgreSQL on my machine then try > again. I found that it has a little improvement about 0.5 second but I > think it still slow. > > What is the 'more detail' you need? Your tables, views and index definitions. > Any other advise? You haven't provided any information on how your tables/views look like and what indexes you have defined. A rule of thumb is to define an index for each column you join on. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Slow GROUP BY query
I have a very simple table set: Transactions: transaction_key PRIMARY KEY client TEXT time TIMESTAMP LineItems transaction_key INT amount INT A query to print the contents of transactions with a sum of the line item amounts provides a very suboptimal result. The problem seems to be the GROUP BY clause as it doesn't use the primary index. Rewriting the query to only group on the transaction_key and returning the max of the other transaction fields results in a query of <1ms. (see queries below) Can anyone shed any light here, I would have expected the queries to take roughly the same time? Out of interest, since we are grouping by transaction_key which is unique, surely the other Transaction fields in the group by could be ignored by the planner? Thanks Stuart (running postgresql 8.2.5 on NetBSD 3) >> Slow query EXPLAIN SELECT t.transaction_key,t.cashier,t.time,SUM(l.amount) FROM Transactions t JOIN LineItems l USING (transaction_key) GROUP BY t.transaction_key,t.cashier,t.time ORDER BY t.transaction_key; QUERY PLAN --- Sort (cost=449.16..454.16 rows=2000 width=32) Sort Key: t.transaction_key -> HashAggregate (cost=314.50..339.50 rows=2000 width=32) -> Hash Join (cost=66.00..262.07 rows=5243 width=32) Hash Cond: (l.transaction_key = t.transaction_key) -> Seq Scan on lineitems l (cost=0.00..117.43 rows=5243 width=16) -> Hash (cost=41.00..41.00 rows=2000 width=24) -> Seq Scan on transactions t (cost=0.00..41.00 rows=2000 width=24) (8 rows) Fast query EXPLAIN SELECT t.transaction_key,MAX(t.cashier),MAX(t.time),SUM(l.amount) FROM Transactions t JOIN LineItems l USING (transaction_key) GROUP BY t.transaction_key ORDER BY t.transaction_key; QUERY PLAN - GroupAggregate (cost=0.00..459.11 rows=2000 width=32) -> Merge Join (cost=0.00..371.68 rows=5243 width=32) Merge Cond: (t.transaction_key = l.transaction_key) -> Index Scan using transactions_pkey on transactions t (cost=0.00..86.25 rows=2000 width=24) -> Index Scan using lineitems_transaction_index on lineitems l (cost=0.00..214.90 rows=5243 width=16) (5 rows) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] JOINing SET returning function.
Hi,
I have a set returning function returning variable number of rows
(RETURNS SETOF RECORD).
I have a table which contains a list of input values for this SRF.
I want to write SQL which will return all the data found there. A
simple test case:
a tble
CREATE TEMP TABLE list (n int);
INSERT INTO list VALUES (1),(3),(5),(6);
CREATE OR REPLACE FUNCTION test_set (IN OUT n int, OUT bar text)
RETURNS SETOF RECORD AS $$
my $n = shift;
for my $x (1..$n) {
return_next { n=> $x, bar => '#' x $x }
};
return undef;
$$ LANGUAGE PLperl;
Now, this works fine:
SELECT select * from test_set(4);
n | bar
---+--
1 | #
2 | ##
3 | ###
4 |
And this works fine too:
SELECT generate_series(1,n) FROM list;
generate_series
-
1
1
2
3
1
2
3
4
5
1
2
3
4
5
6
However I would like to write a code which will work with test_set()
as it does with generate_series:
SELECT test_set(n) FROM list;
ERROR: set-valued function called in context that cannot accept a set
...for which I've tried subselects and JOINs but somehow I fail to
make it work, for instance:
SELECT * FROM list, test_set(n);
ERROR: function expression in FROM may not refer to other relations
of same query level
Is there an obvious solution (except wrapping it in some PL function)?
If it is not clear unitl that point, I want to do equivalent of:
SELECT * FROM test_set(1)
UNION ALL SELECT * FROM test_set(3)
UNION ALL SELECT * FROM test_set(5)
UNION ALL SELECT * FROM test_set(6);
...except dynamically using "n" column from "list" table.
Of course this is a test case, not the original problem. :-)
Regards,
Dawid
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Slow Query problem
SQL I use for create related table and view:
CREATE TABLE t_payment_detail( "sysid" bigserial NOT NULL, receiptno varchar(10) NOT NULL, refpath varchar(255) NOT NULL, refno varchar(100) NOT NULL, CONSTRAINT t_payment_detail_pkey PRIMARY KEY ("sysid")) WITHOUT OIDS;
CREATE TABLE t_receipt_cancel( receiptsysid int8 NOT NULL, amount float8 NOT NULL, receiptcomment varchar(255) NOT NULL, CONSTRAINT t_receipt_cancel_pkey PRIMARY KEY (receiptsysid)) WITH OIDS;
CREATE TABLE t_charge( "sysid" bigserial NOT NULL, mbrsysid int8 NOT NULL, chargelistsysid int8 NOT NULL, refno varchar(20) NOT NULL, chargedate timestamp NOT NULL, quantity float8 NOT NULL, unitprice float8 NOT NULL, amount float8 NOT NULL, vat float8 NOT NULL, service float8 NOT NULL, CONSTRAINT t_charge_pkey PRIMARY KEY ("sysid")) WITH OIDS;
CREATE TABLE t_posbill( "sysid" bigserial NOT NULL, billno varchar(20) NOT NULL DEFAULT ''::character varying, billdate timestamp NOT NULL DEFAULT '1601-01-01 00:00:00'::timestamp without time zone, mbrsysid int8 NOT NULL DEFAULT 0, totalamount float8 NOT NULL DEFAULT 0, totalvat float8 NOT NULL DEFAULT 0, totalservice float8 NOT NULL DEFAULT 0, CONSTRAINT t_posbill_pkey PRIMARY KEY ("sysid")) WITHOUT OIDS;
CREATE TABLE t_creditnotes( "sysid" bigserial NOT NULL, mbrsysid int8 NOT NULL, chargelistsysid int8 NOT NULL, chitno varchar(20) NOT NULL, chitdate timestamp NOT NULL, refno varchar(20) NOT NULL, chargedate timestamp NOT NULL, quantity float8 NOT NULL, unitprice float8 NOT NULL, amount float8 NOT NULL, vat float8 NOT NULL, service float8 NOT NULL, CONSTRAINT t_creditnotes_pkey PRIMARY KEY ("sysid")) WITH OIDS;
CREATE TABLE t_invoice( "sysid" bigserial NOT NULL, mbrsysid int8 NOT NULL, chargelistsysid int8 NOT NULL, invno varchar(50) NOT NULL, invdate timestamp NOT NULL, totalvalue float8 NOT NULL, totalvat float8 NOT NULL, totalservice float8 NOT NULL, CONSTRAINT t_invoice_pkey PRIMARY KEY ("sysid")) WITH OIDS;
CREATE TABLE t_receipt( "sysid" bigserial NOT NULL, mbrsysid int8 NOT NULL, receiptno varchar(10) NOT NULL, receiptdate timestamp NOT NULL, paymethod varchar(30) NOT NULL, amount float8 NOT NULL, flagprint bool NOT NULL DEFAULT false, CONSTRAINT t_receipt_pkey PRIMARY KEY ("sysid")) WITH OIDS;
CREATE TABLE t_moneytransfer( "sysid" bigserial NOT NULL, mbrsysid int8 NOT NULL, transferno varchar(10) NOT NULL, transferdate timestamp NOT NULL, transferamount float8 NOT NULL, CONSTRAINT t_moneytransfer_pkey PRIMARY KEY ("sysid")) WITH OIDS;
CREATE TABLE t_carryforward( "sysid" bigserial NOT NULL, mbrsysid int8 NOT NULL, cfno varchar(10) NOT NULL, cfdate timestamp NOT NULL, amount float8 NOT NULL, CONSTRAINT t_carryforward_pkey PRIMARY KEY ("sysid")) WITH OIDS;
CREATE OR REPLACE VIEW v_invtransaction_main AS ( SELECT 'C'::text || t_charge.refno::text AS transinvno, t_charge.mbrsysid, 'CHIT'::text AS particular, t_charge.chargedate AS transdate, sum(t_charge.amount) AS totamount, sum(t_charge.vat) AS totvat, sum(t_charge.service) AS totservice, 'Dr' AS cr_dr, t_charge.refno AS transrefno FROM t_charge GROUP BY 'C'::text || t_charge.refno::text, t_charge.mbrsysid, 'CHIT'::text , t_charge.chargedate, t_charge.refnoUNION ALL SELECT 'B'::text || t_posbill.billno::text AS transinvno, t_posbill.mbrsysid, 'POS'::text AS particular, t_posbill.billdate AS transdate, t_posbill.totalamount AS totamount, t_posbill.totalvat AS totvat, t_posbill.totalservice AS totservice, 'Dr' AS cr_dr, t_posbill.billno AS transrefno FROM t_posbill)UNION ALL SELECT 'CN'::text || t_creditnotes.refno::text AS transinvno, t_creditnotes.mbrsysid, 'Credit Note'::text AS particular, t_creditnotes.chargedate AS transdate, - sum(t_creditnotes.amount) AS totamount, - sum(t_creditnotes.vat) AS totvat, - sum(t_creditnotes.service) AS totservice, 'Cr' AS cr_dr, t_creditnotes.refno AS transrefno FROM t_creditnotes GROUP BY 'CN'::text || t_creditnotes.refno::text, t_creditnotes.mbrsysid, 'Credit Note'::text, t_creditnotes.chargedate, t_creditnotes.refno)UNION ALL SELECT 'I'::text || t_invoice.invno::text AS transinvno, t_invoice.mbrsysid, 'Monthly Invoice '::text AS particular, t_invoice.invdate AS transdate, sum(t_invoice.totalvalue) AS totamount, sum(t_invoice.totalvat) AS totvat, sum(t_invoice.totalservice) AS totservice, 'Dr' AS cr_dr, t_invoice.invno AS transrefno FROM t_invoice GROUP BY 'I'::text || t_invoice.invno::text, t_invoice.mbrsysid, 'Monthly Invoice'::text, t_invoice.invdate,t_invoice.invno)UNION ALL SELECT 'P'::text || t_receipt.receiptno::text AS transinvno, t_receipt.mbrsysid, t_receipt.paymethod::text AS particular, t_receipt.receiptdate AS transdate, t_receipt.amount * (- 1::double precision) AS totamount, '0' AS totvat, '0' AS totservice, 'Cr' AS cr_dr, t_receipt.receiptno AS transrefno FROM t_receipt WHERE NOT (t_receipt."sysid" IN ( SELECT t_receipt_cancel.receiptsysid FROM t_receipt_cancel)))UNION ALL SEL
Re: [SQL] Slow Query problem
SQL I use for create related table and view:
CREATE TABLE t_payment_detail( "sysid" bigserial NOT NULL, receiptno varchar(10) NOT NULL, refpath varchar(255) NOT NULL, refno varchar(100) NOT NULL, CONSTRAINT t_payment_detail_pkey PRIMARY KEY ("sysid")) WITHOUT OIDS;
CREATE TABLE t_receipt_cancel( receiptsysid int8 NOT NULL, amount float8 NOT NULL, receiptcomment varchar(255) NOT NULL, CONSTRAINT t_receipt_cancel_pkey PRIMARY KEY (receiptsysid)) WITH OIDS;
CREATE TABLE t_charge( "sysid" bigserial NOT NULL, mbrsysid int8 NOT NULL, chargelistsysid int8 NOT NULL, refno varchar(20) NOT NULL, chargedate timestamp NOT NULL, quantity float8 NOT NULL, unitprice float8 NOT NULL, amount float8 NOT NULL, vat float8 NOT NULL, service float8 NOT NULL, CONSTRAINT t_charge_pkey PRIMARY KEY ("sysid")) WITH OIDS;
CREATE TABLE t_posbill( "sysid" bigserial NOT NULL, billno varchar(20) NOT NULL DEFAULT ''::character varying, billdate timestamp NOT NULL DEFAULT '1601-01-01 00:00:00'::timestamp without time zone, mbrsysid int8 NOT NULL DEFAULT 0, totalamount float8 NOT NULL DEFAULT 0, totalvat float8 NOT NULL DEFAULT 0, totalservice float8 NOT NULL DEFAULT 0, CONSTRAINT t_posbill_pkey PRIMARY KEY ("sysid")) WITHOUT OIDS;
CREATE TABLE t_creditnotes( "sysid" bigserial NOT NULL, mbrsysid int8 NOT NULL, chargelistsysid int8 NOT NULL, chitno varchar(20) NOT NULL, chitdate timestamp NOT NULL, refno varchar(20) NOT NULL, chargedate timestamp NOT NULL, quantity float8 NOT NULL, unitprice float8 NOT NULL, amount float8 NOT NULL, vat float8 NOT NULL, service float8 NOT NULL, CONSTRAINT t_creditnotes_pkey PRIMARY KEY ("sysid")) WITH OIDS;
CREATE TABLE t_invoice( "sysid" bigserial NOT NULL, mbrsysid int8 NOT NULL, chargelistsysid int8 NOT NULL, invno varchar(50) NOT NULL, invdate timestamp NOT NULL, totalvalue float8 NOT NULL, totalvat float8 NOT NULL, totalservice float8 NOT NULL, CONSTRAINT t_invoice_pkey PRIMARY KEY ("sysid")) WITH OIDS;
CREATE TABLE t_receipt( "sysid" bigserial NOT NULL, mbrsysid int8 NOT NULL, receiptno varchar(10) NOT NULL, receiptdate timestamp NOT NULL, paymethod varchar(30) NOT NULL, amount float8 NOT NULL, flagprint bool NOT NULL DEFAULT false, CONSTRAINT t_receipt_pkey PRIMARY KEY ("sysid")) WITH OIDS;
CREATE TABLE t_moneytransfer( "sysid" bigserial NOT NULL, mbrsysid int8 NOT NULL, transferno varchar(10) NOT NULL, transferdate timestamp NOT NULL, transferamount float8 NOT NULL, CONSTRAINT t_moneytransfer_pkey PRIMARY KEY ("sysid")) WITH OIDS;
CREATE TABLE t_carryforward( "sysid" bigserial NOT NULL, mbrsysid int8 NOT NULL, cfno varchar(10) NOT NULL, cfdate timestamp NOT NULL, amount float8 NOT NULL, CONSTRAINT t_carryforward_pkey PRIMARY KEY ("sysid")) WITH OIDS;
CREATE OR REPLACE VIEW v_invtransaction_main AS ( SELECT 'C'::text || t_charge.refno::text AS transinvno, t_charge.mbrsysid, 'CHIT'::text AS particular, t_charge.chargedate AS transdate, sum(t_charge.amount) AS totamount, sum(t_charge.vat) AS totvat, sum(t_charge.service) AS totservice, 'Dr' AS cr_dr, t_charge.refno AS transrefno FROM t_charge GROUP BY 'C'::text || t_charge.refno::text, t_charge.mbrsysid, 'CHIT'::text , t_charge.chargedate, t_charge.refnoUNION ALL SELECT 'B'::text || t_posbill.billno::text AS transinvno, t_posbill.mbrsysid, 'POS'::text AS particular, t_posbill.billdate AS transdate, t_posbill.totalamount AS totamount, t_posbill.totalvat AS totvat, t_posbill.totalservice AS totservice, 'Dr' AS cr_dr, t_posbill.billno AS transrefno FROM t_posbill)UNION ALL SELECT 'CN'::text || t_creditnotes.refno::text AS transinvno, t_creditnotes.mbrsysid, 'Credit Note'::text AS particular, t_creditnotes.chargedate AS transdate, - sum(t_creditnotes.amount) AS totamount, - sum(t_creditnotes.vat) AS totvat, - sum(t_creditnotes.service) AS totservice, 'Cr' AS cr_dr, t_creditnotes.refno AS transrefno FROM t_creditnotes GROUP BY 'CN'::text || t_creditnotes.refno::text, t_creditnotes.mbrsysid, 'Credit Note'::text, t_creditnotes.chargedate, t_creditnotes.refno)UNION ALL SELECT 'I'::text || t_invoice.invno::text AS transinvno, t_invoice.mbrsysid, 'Monthly Invoice '::text AS particular, t_invoice.invdate AS transdate, sum(t_invoice.totalvalue) AS totamount, sum(t_invoice.totalvat) AS totvat, sum(t_invoice.totalservice) AS totservice, 'Dr' AS cr_dr, t_invoice.invno AS transrefno FROM t_invoice GROUP BY 'I'::text || t_invoice.invno::text, t_invoice.mbrsysid, 'Monthly Invoice'::text, t_invoice.invdate,t_invoice.invno)UNION ALL SELECT 'P'::text || t_receipt.receiptno::text AS transinvno, t_receipt.mbrsysid, t_receipt.paymethod::text AS particular, t_receipt.receiptdate AS transdate, t_receipt.amount * (- 1::double precision) AS totamount, '0' AS totvat, '0' AS totservice, 'Cr' AS cr_dr, t_receipt.receiptno AS transrefno FROM t_receipt WHERE NOT (t_receipt."sysid" IN ( SELECT t_receipt_cancel.receiptsysid FROM t_receipt_cancel)))UNION ALL SEL
[SQL] Re: Proposed archival read only trigger on rows - prevent history modification
At 07:50 PM 1/29/2008, [EMAIL PROTECTED] wrote:
Date: Mon, 28 Jan 2008 20:16:35 -0800
From: Bryce Nesbitt <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Subject: Proposed archival read only trigger on rows - prevent history
modification
[snip]
I'm considering building a protective mechanism, and am seeking
feedback
on the idea. The approach would be to add a new column named "ro" to
each table at invoice level and below. Then have a trigger on
'ro'==true deny the write, and probably raise a huge stink. As
invoice
are mailed each month, all the supporting data would be set to "ro"
true.
[snip]
Hi Bryce,
I have a similar situation but a little in reverse. I have many sets of
*incoming* records, which I want to preserve, though at any one time
there is only one "live" version of the incoming records. Sometimes I
have to read and compare versions of the records, live or otherwise.
The logical records I'm talking about occupy a number of tables that
are joined together in the database itself.
My solution, which required a little middleware engineering, was to
create two tables for each table that had multiple "versions" of
records. So, I have a "property" table and a "property_versions" table
that have identical table structures. I have a column common to every
such versioned set of tables called "import_group_id." For live tables,
this just tells me which version from the "*_versions" table is
currently being used for that row. The live tables have primary keys
just like normal ("id" as a serial int field). The "versions" tables'
primary keys are different, compound keyed off "id" and
"import_group_id." This permits normalization but also allows multiple
versions of the same records.
In your case, I'd say you could archive your data table to a
"data_versions" table. You might archive periodically and leave the
records on the live table (but knowing that audit versions are safely
tucked away and easily accessible/comparable), or you might migrate the
records off the live table onto the versions table (insert followed by
a delete in a transaction). If you adopted the latter method, you could
union the two tables to get a complete set of rows. (Via a view even?
Not sure if you can create a view on a union but it seems likely you
can..)
You could also choose (like I did) to store multiple versions of the
records, if your data are slowly changing rather than completely
unchanging. However it sounds like your situation is such that you want
to ensure old records are not modified ever. If you set up triggers
and/or permission on the "data_versions" table, no one can ever delete
or modify anything there. This might work perfectly for your purposes.
Data in the live table can be edited as you like but changes to the
"data_versions" table is simply not permitted. Inserts are allowed to
data_versions but nothing else..
I hope this idea is useful. If I haven't explained it well, drop me a
line and I'll try to clarify. Good luck with the project!
Steve
---(end of broadcast)---
TIP 1: 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
