In response to Matthew <matthew@flymine.org>:

> On Tue, 27 Nov 2007, Pablo Alcaraz wrote:
> > it would be nice to do something with selects so we can recover a rowset
> > on huge tables using a criteria with indexes without fall running a full
> > scan.
>
> You mean: Be able to tell Postgres "Don't ever do a sequential scan of
> this table. It's silly. I would rather the query failed than have to wait
> for a sequential scan of the entire table."
>
> Yes, that would be really useful, if you have huge tables in your
> database.

Is there something wrong with:
set enable_seqscan = off
?

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Wed, 2007-11-28 at 08:27 -0500, Bill Moran wrote:
> Is there something wrong with:
> set enable_seqscan = off
> ?

Nothing wrong with enable_seqscan = off except it is all or nothing type
of thing... if you want the big table to never use seqscan, but a medium
table which is joined in should use it, then what you do ? And setting
enable_seqscan = off will actually not mean the planner can't use a
sequential scan for the query if no other alternative exist. In any case
it doesn't mean "please throw an error if you can't do this without a
sequential scan".

In fact an even more useful option would be to ask the planner to throw
error if the expected cost exceeds a certain threshold...

Cheers,
Csaba.



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

In response to Csaba Nagy <nagy@ecircle-ag.com>:

> On Wed, 2007-11-28 at 08:27 -0500, Bill Moran wrote:
> > Is there something wrong with:
> > set enable_seqscan = off
> > ?
>
> Nothing wrong with enable_seqscan = off except it is all or nothing type
> of thing...

If that's true, then I have a bug report to file:

test=# set enable_seqscan=off;
SET
test=# show enable_seqscan;
enable_seqscan
----------------
off
(1 row)

test=# set enable_seqscan=on;
SET
test=# show enable_seqscan;
enable_seqscan
----------------
on
(1 row)

It looks to me to be session-alterable.

> if you want the big table to never use seqscan, but a medium
> table which is joined in should use it, then what you do ? And setting
> enable_seqscan = off will actually not mean the planner can't use a
> sequential scan for the query if no other alternative exist. In any case
> it doesn't mean "please throw an error if you can't do this without a
> sequential scan".

True. It would still choose some other plan.

> In fact an even more useful option would be to ask the planner to throw
> error if the expected cost exceeds a certain threshold...

Interesting concept.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Bill Moran" <wmoran@collaborativefusion.com> writes:

> In response to Matthew <matthew@flymine.org>:
>
>> On Tue, 27 Nov 2007, Pablo Alcaraz wrote:
>> > it would be nice to do something with selects so we can recover a rowset
>> > on huge tables using a criteria with indexes without fall running a full
>> > scan.
>>
>> You mean: Be able to tell Postgres "Don't ever do a sequential scan of
>> this table. It's silly. I would rather the query failed than have to wait
>> for a sequential scan of the entire table."
>>
>> Yes, that would be really useful, if you have huge tables in your
>> database.
>
> Is there something wrong with:
> set enable_seqscan = off
> ?

This does kind of the opposite of what you would actually want here. What you
want is that if you give it a query which would be best satisfied by a
sequential scan it should throw an error since you've obviously made an error
in the query.

What this does is it forces such a query to use an even *slower* method such
as a large index scan. In cases where there isn't any other method it goes
ahead and does the sequential scan anyways.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Wed, 28 Nov 2007, Csaba Nagy wrote:

> On Wed, 2007-11-28 at 08:27 -0500, Bill Moran wrote:
>> Is there something wrong with:
>> set enable_seqscan = off
>> ?
>
> Nothing wrong with enable_seqscan = off except it is all or nothing type
> of thing... if you want the big table to never use seqscan, but a medium
> table which is joined in should use it, then what you do ? And setting
> enable_seqscan = off will actually not mean the planner can't use a
> sequential scan for the query if no other alternative exist. In any case
> it doesn't mean "please throw an error if you can't do this without a
> sequential scan".
>
> In fact an even more useful option would be to ask the planner to throw
> error if the expected cost exceeds a certain threshold...

and even better if the option can be overridden for a specific transaction
or connection. that way it can be set relativly low for normal operations,
but when you need to do an expensive query you can change it for that
query.

David Lang

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

In response to Gregory Stark <stark@enterprisedb.com>:

> "Bill Moran" <wmoran@collaborativefusion.com> writes:
>
> > In response to Matthew <matthew@flymine.org>:
> >
> >> On Tue, 27 Nov 2007, Pablo Alcaraz wrote:
> >> > it would be nice to do something with selects so we can recover a rowset
> >> > on huge tables using a criteria with indexes without fall running a full
> >> > scan.
> >>
> >> You mean: Be able to tell Postgres "Don't ever do a sequential scan of
> >> this table. It's silly. I would rather the query failed than have to wait
> >> for a sequential scan of the entire table."
> >>
> >> Yes, that would be really useful, if you have huge tables in your
> >> database.
> >
> > Is there something wrong with:
> > set enable_seqscan = off
> > ?
>
> This does kind of the opposite of what you would actually want here. What you
> want is that if you give it a query which would be best satisfied by a
> sequential scan it should throw an error since you've obviously made an error
> in the query.
>
> What this does is it forces such a query to use an even *slower* method such
> as a large index scan. In cases where there isn't any other method it goes
> ahead and does the sequential scan anyways.

Ah. I misunderstood the intent of the comment.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Wed, 2007-11-28 at 08:54 -0500, Bill Moran wrote:
> > Nothing wrong with enable_seqscan = off except it is all or nothing type
> > of thing...
>
> If that's true, then I have a bug report to file:
[snip]
> It looks to me to be session-alterable.

I didn't mean that it can't be set per session, I meant that it is not
fine grained enough to select the affected table but it affects _all_
tables in a query... and big tables are rarely alone in a query.

Cheers,
Csaba.



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Matthew wrote:
> On Tue, 27 Nov 2007, Pablo Alcaraz wrote:
>
>> it would be nice to do something with selects so we can recover a rowset
>> on huge tables using a criteria with indexes without fall running a full
>> scan.
>>
>
> You mean: Be able to tell Postgres "Don't ever do a sequential scan of
> this table. It's silly. I would rather the query failed than have to wait
> for a sequential scan of the entire table."
>
> Yes, that would be really useful, if you have huge tables in your
> database.
>

Thanks. That would be nice too. I want that Postgres does not fall so
easy to do sequential scan if a field are indexed. if it concludes that
the index is *huge* and it does not fit in ram I want that Postgresql
uses the index anyway because the table is *more than huge* and a
sequential scan will take hours.

I ll put some examples in a next mail.

Regards

Pablo

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Wed, 28 Nov 2007, Gregory Stark wrote:
> > Is there something wrong with:
> > set enable_seqscan = off
> > ?
>
> This does kind of the opposite of what you would actually want here. What you
> want is that if you give it a query which would be best satisfied by a
> sequential scan it should throw an error since you've obviously made an error
> in the query.
>
> What this does is it forces such a query to use an even *slower* method such
> as a large index scan. In cases where there isn't any other method it goes
> ahead and does the sequential scan anyways.

The query planner is not always right. I would like an option like
"set enable_seqscan = off" but with the added effect of making Postgres
return an error if there is no alternative to scanning the whole table,
because I have obviously made a mistake setting up my indexes. I would
effectively be telling Postgres "For this table, I *know* that a full
table scan is dumb for all of my queries, even if the statistics say
otherwise."

Of course, it would have to be slightly intelligent, because there are
circumstances where a sequential scan doesn't necessarily mean a full
table scan (for instance if there is a LIMIT), and where an index scan
*does* mean a full table scan (for instance, selecting the whole table and
ordering by an indexed field).

Matthew

--
Existence is a convenient concept to designate all of the files that an
executable program can potentially process. -- Fortran77 standard

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Pablo Alcaraz wrote:
> Simon Riggs wrote:
>> All of those responses have cooked up quite a few topics into one. Large
>> databases might mean text warehouses, XML message stores, relational
>> archives and fact-based business data warehouses.
>>
>> The main thing is that TB-sized databases are performance critical. So
>> it all depends upon your workload really as to how well PostgreSQL, or
>> another other RDBMS vendor can handle them.
>>
>>
>> Anyway, my reason for replying to this thread is that I'm planning
>> changes for PostgreSQL 8.4+ that will make allow us to get bigger and
>> faster databases. If anybody has specific concerns then I'd like to hear
>> them so I can consider those things in the planning stages
> it would be nice to do something with selects so we can recover a
> rowset on huge tables using a criteria with indexes without fall
> running a full scan.
>
> In my opinion, by definition, a huge database sooner or later will
> have tables far bigger than RAM available (same for their indexes). I
> think the queries need to be solved using indexes enough smart to be
> fast on disk.
>
> Pablo

I am dealing with a very huge database. I am not sure if all these
things could be solved with the current Postgres version using somes
configuration parameters. I ll be happy to read your suggestions and
ideas about these queries.

In my opinion there are queries that I think they ll need to be tuned
for "huge databases" (huge databases = a database which relevant
tables(indexes) are (will be) far bigger that all the ram available):

-- example table
CREATE TABLE homes (
id bigserial,
name text,
location text,
bigint money_win,
int zipcode;
);
CREATE INDEX money_win_idx ON homes(money_win);
CREATE INDEX zipcode_idx ON homes(zipcode);


SELECT max( id) from homes;
I think the information to get the max row quickly could be found using
the pk index. Idem min( id).

SELECT max( id) from homes WHERE id > 8000000000;
Same, but useful to find out the same thing in partitioned tables (using
id like partition criteria). It would be nice if Postgres would not need
the WHERE clause to realize it does not need to scan every single
partition, but only the last. Idem min(id).

SELECT * from homes WHERE money_win = 1300000000;
Postgres thinks too easily to solve these kind of queries that it must
to do a sequential scan where the table (or the index) does not fix in
memory if the number of rows is not near 1 (example: if the query
returns 5000 rows). Same case with filters like 'WHERE money_win >= xx',
'WHERE money_win BETWEEN xx AND yy'. But I do not know if this behavior
is because I did a wrong posgresql's configuration or I missed something.

SELECT count( *) from homes;
it would be *cute* that Postgres stores this value and only recalculate
if it thinks the stored value is wrong (example: after an anormal
shutdown).

SELECT zipcode, count( zipcode) as n from homes GROUP BY zipcode;
it would be *very cute* that Postgres could store this value (or is this
there?) on the index or wherever and it only recalculates if it thinks
the stored value is wrong (example: after an anormal shutdown).

In my opinion, partitioned tables in "huge databases" would be the
usual, not the exception. It would be important (for me at least) that
these queries could be fast solved when they run in partitioned tables.

Maybe one or more of these queries could be solved using some kind of
optimization. But I do not discover which ones (I ll be happy to read
suggestions ). I am sure a lot/all these queries could be solved using
some kind of triggers/sequence to store information to solve the stuff.
But in general the information is there right now (is it there?) and the
queries only need that the server could look in the right place. A
trigger/function using some pgsql supported languages probably will
consume far more CPU resources to find out the same information that
exist right now and we need to do it using transactions (more perfomance
costs) only to be sure we are fine if the server has an anormal shutdown.

Currently I have several 250Gb+ tables with billions of rows (little
rows like the homes table example). I partitioned and distributed the
partitions/index in different tablespaces, etc. I think "I did not need"
so much partitions like I have right now (300+ for some tables and
growing). I just would need enough partitions to distribute the tables
in differents tablespaces. I did so much partitions because the
perfomance with really big tables is not enough good for me when the
programs run these kind of queries and the insert/update speed is worst
and worst with the time.

I hope that a couple of tables will be 1Tb+ in a few months... buy more
and more RAM is an option but not a solution because eventually the
database will be far bigger than ram available.

Last but not least, it would be *excelent* that this kind of
optimization would be posible without weird non standard sql sentences.
I think that Postgresql would be better with huge databases if it can
solve for itself these kind of queries in the fastest way or at least we
are abled to tell it to choice a different criteria. I could help it
using postgresql.conf to activate/deactivate some behavior or to use
some system table to tell the criteria I want with some tables (like
autovacuum does right now with table exception vacuums) or using non
standard DDL to define that criteria.

But the thing is that the programmers must be able to use standard SQL
for selects/inserts/updates/deletes with 'where' and 'group by' clauses.
In my case the programs are builded with java + JPA, so standard SQL
(but no DDL) is important to keep the things like they are.

Well, that's my 2cents feedback.

Regards

Pablo

PD: Sorry my broken english.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster