From: | "NTPT" <ntpt(at)centrum(dot)cz> |
---|---|
To: | "Steve Atkins" <steve(at)blighty(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Specifying many rows in a table |
Date: | 2004-01-30 11:38:43 |
Message-ID: | 003301c3e725$9d7b1000$d300a8c0@webpropag.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
----- Puvodní zpráva -----
Od: "Steve Atkins" <steve(at)blighty(dot)com>
Komu: <pgsql-general(at)postgresql(dot)org>
Odesláno: 28. ledna 2004 20:32
Predmet: [GENERAL] Specifying many rows in a table
> I have a large table (potentially tens or hundreds of millions of rows) and
> I need to extract some number of these rows, defined by an integer primary
> key.
>
> So, the obvious answer is
>
> select * from table where id in (1,3,4);
Should not it be select * from table where id in ('1','3','4'); ?
ie add an single quote around the numbers ? Think I red some statement in documentation, that without a single quote, index scan may not be always used ?
>
> But I may want to extract a large number of rows, many thousands
>
> select * from table where id in (1, 3, 5, ...., 100000, 100017, 23000);
>
> This falls over when it exceeds the maximum expression depth of 10,000.
> And I have a sneaky feeling that increasing max_expr_depth isn't the
> right fix.
>
> Performance is pretty important, so does anyone have a good suggestion
> for how to phrase this query so that it'll at worst only be a single
> seq-scan through the mondo table, and make a reasonable choice as to
> whether to use an index-scan or seq-scan, depending on the number
> of rows I'm pulling out?
>
> Cheers,
> Steve
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Atkins | 2004-01-30 14:11:41 | Re: Specifying many rows in a table |
Previous Message | Paul Thomas | 2004-01-30 11:37:35 | Re: IDENT and pg_hda.conf |