[HACKERS] calculated fields are not seen in the WHERE clause

[Yaroslav Dmitriev]

Hello,

OK
select 1 as ccc where 1=1

ERROR
select 1 as ccc where ccc=1
PostgreSQL said: ERROR: Attribute 'ccc' not found

Is there any way to set conditions on calculated fields values?

Best regards,
Yar



---(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

[HACKERS] []performance issues

[Yaroslav Dmitriev]

Hello,

Sorry if it's wrong list for the question. Could you suggest some tweaks 
to the PostgreSQL 7.2.1 to handle the following types of tables faster?

Here we have  table stats with  something over one millon records. 
Obvious SELECT COUNT(*) FROM stats  takes over 40 seconds to execute, 
and this amount of time does not shorten considerably in subsequent 
similar requests. All the databases are vacuumed nightly.

CREATE TABLE stats (
   url varchar(50),
   src_port varchar(10),
   ip varchar(16),
   dst_port varchar(10),
   proto varchar(10),
   size int8,
   login varchar(20),
   start_date timestamptz,
   end_date timestamptz,
   aggregated int4
);
CREATE  INDEX aggregated_stats_key ON stats (aggregated);
CREATE  INDEX ip_stats_key ON stats (ip);

stats= explain select count(*) from stats;
NOTICE:  QUERY PLAN:

Aggregate  (cost=113331.10..113331.10 rows=1 width=0)
  -  Seq Scan on stats  (cost=0.00..110085.28 rows=1298328 width=0)

EXPLAIN
stats= select count(*) from stats;
  count  
-
 1298328
(1 row)

The system is FreeBSD-4.6-stable, softupdates on, Athlon XP 1500+, 512 Mb DDR, ATA 100 
HDD.

Thanks in advance,
Yar



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] []performance issues

[Yaroslav Dmitriev]

Christopher Kings-Lynne wrote:

Doing a row count requires a sequential scan in Postgres.

Try creating another summary table that just has one row and one column and
is an integer.
  


I have THREE  summary tables derived from stats with different levels 
of aggregation. They work quite fast,  But:

1) Summary tables grow too
2) There are requests which cannot be predicted, so they involve the 
stats table itself.

So I am still interested in PostgreSQL's ability to deal with 
multimillon records tables.

Best regards,
Yar.


---(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