pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizing view
2+ messages / 2 participants
[nested] [flat]
* Optimizing view
@ 2003-03-10 13:52 Victor Yegorov <viy@pirmabanka.lv>
2003-03-10 15:14 ` Re: Optimizing view Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Victor Yegorov @ 2003-03-10 13:52 UTC (permalink / raw)
To: pgsql-sql
Hello.
I'm running `PostgreSQL 7.2 on i586-pc-linux-gnu, compiled by GCC 2.95.3'
and have some problems with queries on a view.
I've got two tables: working one and archive and one view, I've attached the description.
And here is my problem: queries on a view runs to much time.
explain analyze select status, target, attempts from messages_all where message_id = 21823;
NOTICE: QUERY PLAN:
Subquery Scan messages_all (cost=2410.78..2887.07 rows=1270 width=210) (actual time=5946.24..5948.41 rows=1 loops=1)
-> Unique (cost=2410.78..2887.07 rows=1270 width=210) (actual time=4567.78..5765.08 rows=12857 loops=1)
-> Sort (cost=2410.78..2410.78 rows=12701 width=210) (actual time=4567.75..5043.40 rows=12857 loops=1)
-> Append (cost=0.00..764.01 rows=12701 width=210) (actual time=0.62..2346.45 rows=12857 loops=1)
-> Subquery Scan *SELECT* 1 (cost=0.00..287.17 rows=917 width=210) (actual time=0.61..229.99 rows=1073 loops=1)
-> Seq Scan on messages (cost=0.00..287.17 rows=917 width=210) (actual time=0.52..157.78 rows=1073 loops=1)
-> Subquery Scan *SELECT* 2 (cost=0.00..476.84 rows=11784 width=207) (actual time=3.13..2037.05 rows=11784 loops=1)
-> Seq Scan on messages_archive (cost=0.00..476.84 rows=11784 width=207) (actual time=3.04..1216.87 rows=11784 loops=1)
Total runtime: 6101.58 msec
EXPLAIN
Too lo-o-ong for 13 000 tuples.
When I UNION 2 queries from this tables, query is pretty fast:
explain analyze select status, target, attempts from messages where message_id = 21823 union select status, target, attempts from messages_archive where message_id = 21823;
NOTICE: QUERY PLAN:
Unique (cost=7.34..7.36 rows=1 width=19) (actual time=224.32..224.34 rows=1 loops=1)
-> Sort (cost=7.34..7.34 rows=2 width=19) (actual time=224.31..224.31 rows=1 loops=1)
-> Append (cost=0.00..7.33 rows=2 width=19) (actual time=128.29..205.15 rows=1 loops=1)
-> Subquery Scan *SELECT* 1 (cost=0.00..4.24 rows=1 width=19) (actual time=128.27..128.47 rows=1 loops=1)
-> Index Scan using messages_primary on messages (cost=0.00..4.24 rows=1 width=19) (actual time=128.23..128.42 rows=1 loops=1)
-> Subquery Scan *SELECT* 2 (cost=0.00..3.10 rows=1 width=19) (actual time=76.65..76.65 rows=0 loops=1)
-> Index Scan using messages_archive_id on messages_archive (cost=0.00..3.10 rows=1 width=19) (actual time=76.63..76.63 rows=0 loops=1)
Total runtime: 225.35 msec
EXPLAIN
I've read manual on REWRITE RULES, but I can't get, how to create RULE to keep the
WHERE clause and separate one query into 2, and union them after.
Any suggestions?
--
Victor Yegorov
Attachments:
db-objects.sql (text/plain, 1.4K) download
CREATE TABLE messages (
message_id INT4 NOT NULL,
target VARCHAR(99),
client_id INT NOT NULL,
content VARCHAR(999),
time_in TIMESTAMP NOT NULL,
time_out TIMESTAMP,
time_got TIMESTAMP,
status INT NOT NULL,
attempts INT NOT NULL,
priority INT NOT NULL DEFAULT 0,
notify INT,
device INT,
delay INT,
validity INT NOT NULL DEFAULT -1,
route_id INT,
CONSTRAINT messages_primary PRIMARY KEY(message_id),
CONSTRAINT messages_client_id
FOREIGN KEY(client_id) REFERENCES clients(client_id),
CONSTRAINT messages_status
FOREIGN KEY(status) REFERENCES mstatus(status)
);
CREATE TABLE messages_archive (
message_id INT4 NOT NULL,
target VARCHAR(99),
client_id INT NOT NULL,
content VARCHAR(999),
time_in TIMESTAMP NOT NULL,
time_out TIMESTAMP,
time_got TIMESTAMP,
status INT NOT NULL,
attempts INT NOT NULL,
priority INT NOT NULL,
notify INT,
device INT,
delay INT,
validity INT NOT NULL,
route_id INT
);
CREATE VIEW messages_all AS
SELECT * FROM messages
UNION SELECT * FROM messages_archive;
* Re: Optimizing view
2003-03-10 13:52 Optimizing view Victor Yegorov <viy@pirmabanka.lv>
@ 2003-03-10 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2003-03-10 15:14 UTC (permalink / raw)
To: Victor Yegorov <viy@pirmabanka.lv>; +Cc: pgsql-sql
"Victor Yegorov" <viy@pirmabanka.lv> writes:
> I'm running `PostgreSQL 7.2 on i586-pc-linux-gnu, compiled by GCC 2.95.3'
> and have some problems with queries on a view.
Try 7.3. 7.2 won't push down WHERE conditions into a union.
BTW, if the archive table is disjoint from the main table, you'd be
better off using UNION ALL instead of UNION.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-03-10 15:14 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-10 13:52 Optimizing view Victor Yegorov <viy@pirmabanka.lv>
2003-03-10 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox