"CN" <[EMAIL PROTECTED]> writes:
> I thought I have got no more question in this thread, and I was wrong :-(
> Is the extra 300ms in UNION essential?
Dividing, it looks like the Append node is taking about 3 microseconds
per tuple, which seems kind of a lot considering it isn't really doing
anythi
Hi!
I thought I have got no more question in this thread, and I was wrong :-(
Is the extra 300ms in UNION essential?
Best Regards,
CN
--This table contains 1036 rows.
CREATE TABLE table1 (
c1 VARCHAR(20) PRIMARY KEY,
c2 "char"
)WITHOUT OIDS;
-
--This table contains 9429 row
> The reason why view1 isn't well optimized is that you've been sloppy
> about datatypes. It looks to me like the "SELECT CASE" business yields
> NUMERIC while the other arm of the UNION yields INTEGER for table4.c3.
> For various subtle semantic reasons we do not try to push down
> conditions int
"CN" <[EMAIL PROTECTED]> writes:
> I thought since both
> SELECT * FROM view1 WHERE year > 2003
> and
> SELECT * FROM view2 WHERE year > 2003
> returns 0 rows, subquery in view1 should consume no CPU and thus both
> queries should consume roughly the same amount of time.
The reason view1 is a lot
Greetings! Stephan,
> So, in practice you'll actually be doing queries with equality rather than ranges?
I'm so glad being understood :-)
> > There is still one thing I don't know why - query on view1 being
> > extermely slow. I also removed the subquery
> > from view1 to form view2. The query o
On Thu, 2 Oct 2003, CN wrote:
> I am trying to explain the meaning of tables and views:
> The tables in the first SELECT in the UNION of view1 are jorunal, whose
> rows are entered daily.
> The table in the second SELECT in the UNION of view1 is budget, whose
> rows use year+month as key.
> View1
Stephan and Tom,
Many thanks!
I am trying to explain the meaning of tables and views:
The tables in the first SELECT in the UNION of view1 are jorunal, whose
rows are entered daily.
The table in the second SELECT in the UNION of view1 is budget, whose
rows use year+month as key.
View1 unions journ
On Wed, 1 Oct 2003, CN wrote:
> > You do realize that extract returns a double precision value not an
> > integer, and it's probably not going to be willing to push clauses down
> > through the union where the types are different .
> >
>
> Argh! I didn't noticed that. Thanks for the reminder.
>
>
"CN" <[EMAIL PROTECTED]> writes:
> Is creating a function that eats DATE as argument to build that index my
> only solution?
You haven't really explained what your problem is. In general I'd not
guess that "year >= X and month >= Y" is a useful operation if Y is
different from 1. You'd be select
> You do realize that extract returns a double precision value not an
> integer, and it's probably not going to be willing to push clauses down
> through the union where the types are different .
>
Argh! I didn't noticed that. Thanks for the reminder.
Let's do not consider table2 and view1 for th
On Wed, 1 Oct 2003, CN wrote:
> Peter, Thanks a lot!
>
> > Unqualified count() cannot use an index because it has to visit all the
> > rows in the table.
>
> It is only for my test. In my real practice, queries like
> "SELECT * FROM view1 WHERE year = 2003 AND month BETWEEN 10 AND 12"
> will be p
> >-> Seq Scan on table1 (cost=0.00..20.00 rows=1000 width=0) (actual
>
> Run VACUUM ANALYZE, then repost your EXPLAIN ANALYZE results please.
QUERY
PLAN
--
>-> Seq Scan on table1 (cost=0.00..20.00 rows=1000 width=0) (actual
Run VACUUM ANALYZE, then repost your EXPLAIN ANALYZE results please.
signature.asc
Description: This is a digitally signed message part
"CN" <[EMAIL PROTECTED]> writes:
> The following view is, again, a simplified version. The real version,
> which takes 13 seconds, joins 2 more tables.
You're really doing your best to make sure we don't figure out what's
going on :-(
One thing I can see from your EXPLAIN ANALYZE results, though,
Peter, Thanks a lot!
> Unqualified count() cannot use an index because it has to visit all the
> rows in the table.
It is only for my test. In my real practice, queries like
"SELECT * FROM view1 WHERE year = 2003 AND month BETWEEN 10 AND 12"
will be performed.
> Then again, I don't quite believe
CN writes:
> Table1 contains 9000 rows and table2 contains 0 row. This query, which
> takes 13489 msec, is extremely slow as pgsql sequentially scans all rows
> in table1:
>
> EXPLAIN ANALYZE SELECT COUNT(*) FROM view1;
Unqualified count() cannot use an index because it has to visit all the
rows
Hi!
CREATE TABLE table1
( d DATE PRIMARY KEY,
amount INTEGER
);
CREATE TABLE table2
( PRIMARY KEY (y,m),
y INTEGER,
m INTEGER
amount INTEGER
);
CREATE VIEW view1 AS
SELECT EXTRACT(YEAR FROM d) AS year, EXTRACT(MONTH FROM d) AS month,
amount
UNION ALL
SELECT * from table2;
Table1 contain
17 matches
Mail list logo
