Re: [SQL] Multiple outer join on same table
On ÐÐÐ, 2004-02-09 at 19:12 +0100, Marco Lazzeri wrote: > Hi! > I'm searching a better (quicker) way to retrieve data as I used to do > using the following query... > > == > SELECT > main.codice, > other.value AS value_one, > other.value AS value_two > FROM main LEFT OUTER JOIN otherON main.id = > other.id_main > LEFT OUTER JOIN other AS other2 ON main.id = > other2.id_main > WHERE > other.type = 'type_one' > AND other2.type = 'type_two'; > == You can alias a table to join it to itself. I'm not sure whether that answers your question. SELECT t1.field FROM table AS t1 JOIN table as t2 on (cond) -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] 7.4 - FK constraint performance
PostgreSQL 7.4 on i386-linux-gnu, compiled by GCC 2.96 -- about 10 records CREATE TABLE my.Small ( id my.dint NOT NULL, code my.dvalue NOT NULL, CONSTRAINT pk_1 PRIMARY KEY (id), ) WITHOUT OIDS; -- about 80M rows CREATE TABLE my.Large ( id my.dlong NOT NULL, small_id my.dint NOT NULL, value my.value, CONSTRAINT pk_2 PRIMARY KEY (id), CONSTRAINT fk_2 FOREIGN KEY (small_id) REFERENCES my.small (id) ON UPDATE RESTRICT ON DELETE RESTRICT, ) WITHOUT OIDS; CREATE INDEX small_fk ON my.Large USING btree (small_id); - The fowllowing queiries run in less than 40 ms. 1) select 1 from Large where small_id = 239 2) SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = 239 FOR UPDATE OF x 3) delete from Small where id = 239 Feb 11 18:07:36 srv07 postgres[2091]: [91-1] LOG: statement: SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = $1 FOR UPDATE OF x Feb 11 18:10:41 srv07 postgres[2091]: [92-1] LOG: duration: 185273.262 ms When I try to delete record, it takes > 3 min. Why is it taking so long if practically the same select query (see (2)) is running very quickly. Anything that can be done to fix it? Thanks __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html ---(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] 7.4 - FK constraint performance
On Wed, 11 Feb 2004, ow wrote: > PostgreSQL 7.4 on i386-linux-gnu, compiled by GCC 2.96 > > -- about 10 records > CREATE TABLE my.Small > ( > id my.dint NOT NULL, > code my.dvalue NOT NULL, > CONSTRAINT pk_1 PRIMARY KEY (id), > ) WITHOUT OIDS; > > -- about 80M rows > CREATE TABLE my.Large > ( > id my.dlong NOT NULL, > small_id my.dint NOT NULL, > value my.value, > CONSTRAINT pk_2 PRIMARY KEY (id), > CONSTRAINT fk_2 FOREIGN KEY (small_id) REFERENCES my.small (id) ON UPDATE > RESTRICT ON DELETE RESTRICT, > ) WITHOUT OIDS; > > CREATE INDEX small_fk ON my.Large USING btree (small_id); > > - > The fowllowing queiries run in less than 40 ms. > 1) select 1 from Large where small_id = 239 > 2) SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = 239 FOR UPDATE OF x > > 3) delete from Small where id = 239 > Feb 11 18:07:36 srv07 postgres[2091]: [91-1] LOG: statement: SELECT 1 FROM > ONLY "my"."Large" x WHERE "small_id" = $1 FOR UPDATE OF x Feb 11 18:10:41 srv07 > > postgres[2091]: [92-1] LOG: duration: 185273.262 ms > > When I try to delete record, it takes > 3 min. Why is it taking so long if > practically the same select query (see (2)) is running very quickly. Anything > that can be done to fix it? Hmm, I'd wonder if maybe it's choosing a sequential scan in the second case? As a random question, does increasing the statistics target on Large.small_id and re-analyzing change its behavior? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] 7.4 - FK constraint performance
ow <[EMAIL PROTECTED]> writes: > When I try to delete record, it takes > 3 min. Why is it taking so long if > practically the same select query (see (2)) is running very quickly. Anything > that can be done to fix it? What are those column datatypes? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] cascade delete
hi why a cascade delete is so slow ? pgsql 7.3.3 ---(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
